November 17, 2009 at 4:25 am
Hi all,
my table structure is,
saNumber int
flag int
amt int
i want to update the flag field when the saNumber is same.
for example
saNumber flag amt
101 1000
101 200
102 300
then only for the first and 2nd record i should update the flag value as 1 and for the third record it will be 0.
how to do it?
thanks,
regards,
anamika
November 17, 2009 at 4:30 am
How do you know which record is first, second, and third?
The records don't appear to be numbered, or have any obvious sequence.
Sure, you listed them in an order, but that can't be it, right?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 17, 2009 at 5:02 am
UPDATE [Table]
SET flag = 1
WHERE saNumber IN(SELECT saNumber FROM [Table] GROUP BY saNumber HAVING COUNT(*) > 1)
Dave
November 17, 2009 at 8:04 pm
Thanks Dave,
is there a way to update a field with a newly generated unique id of all records having the same saNumber?
for ex. all '101' should have the same unique id
and all '102' should have the same unique id etc...
I'm sorry Paul i think i didn't post the question clearly.
thanks,
regards
anamika
November 17, 2009 at 8:16 pm
November 17, 2009 at 8:22 pm
If you need sequential numbers, use DENSE_RANK.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 17, 2009 at 9:07 pm
Hi,
Thanks for your inputs.
but i don't need ranks on those columns.
i want to update a field with newly generated unique identifier in all the saNumber which are same.
for example,
all the records containing '101' as saNumber should have a newly generated unique identifier.
all the records containing '102' as saNumber should have a newly generated unique identifier.
i tried with the following
UPDATE [Table]
SET
ID =( select newid()),
Flag = 1
WHERE saNumber IN(SELECT saNumber FROM [Table] GROUP BY saNumber HAVING COUNT(*) > 1)
but it gives a individual unique number for every records. but i want to generate one common unique number for duplicate records.
thanks,
regards
anamika
November 17, 2009 at 9:19 pm
Hi,
Its may suits you, Try this
UPDATE [Table]
SET
ID = (saNumber*100) ,
Flag = 1
WHERE saNumber IN(SELECT saNumber FROM [Table] GROUP BY saNumber HAVING COUNT(*) > 1)
November 17, 2009 at 9:23 pm
Hi arun,
thanks for ur reply.
but 'id' column data type is unique identifier( i can't change the data type).
so i want to generate one unique identifier and apply it to all saNumber which is same.
regards
anamika
November 17, 2009 at 9:31 pm
declare @id uniqueidentifier
set @id = NEWID()
UPDATE [Table]
SET
ID = @id ,
Flag = 1
WHERE saNumber IN(SELECT saNumber FROM [Table] GROUP BY saNumber HAVING COUNT(*) > 1)
November 17, 2009 at 11:04 pm
HI,
your code will not change the value of @id when the saNumber changes.
all records having '101' should have one unique identifier number
all records having '102' should have one unique identifier number
all records having '103' should have one unique identifier number
like that it goes.....
thanks
anamika
November 17, 2009 at 11:31 pm
Hi,
Thank for mentioned my statement updates only the unique value to all rows,
First you insert the UID into the temp table
Like
SELECT saNumber,NEWID()TEMPID1 into #TEMPID
FROM [Table]
GROUP BY saNumberthen use the temp table to achive
like
UPDATE a
SET
a.ID = c.TEMPID1 ,
a.Flag = 1
from
[Table] a,
(
SELECT saNumber FROM [Table] GROUP BY saNumber HAVING COUNT(*) > 1
)b,
#TEMPID c
where
a.saNumber = b.saNumber
and a.saNumber = c.saNumber
November 18, 2009 at 3:13 am
Hi arun,
this works fine.
but the saNumber, what we are comparing is not the same field always.
sometimes it is raNUmber and vaNumber as we are gathering information from various sources.
so i tried to put that in a dynamic query to get the result.
DECLARE CSR CURSOR FOR SELECT System, Number FROM dbo.Source1
DECLARE @System AS NVARCHAR(50)
,@Number AS NVARCHAR(50)
,@SQL AS VARCHAR(MAX)
,@TEMPSQL AS VARCHAR(MAX)
OPEN CSR
FETCH NEXT FROM CSR INTO @System, @Number
WHILE @@FETCH_STATUS =0
BEGIN
IF EXISTS (
SELECT *
FROM sys.tables
WHERE name='#TEMPID')
DROP TABLE #TEMPID
SET @TEMPSQL = 'SELECT ' +@Number + ', NEWID()TEMPID1 into #TEMPID
FROM dbo.Application1
WHERE ' +@Number + ' IS NOT NULL
GROUP BY '+ @Number
exec(@TEMPSQL)
SET @SQL = 'UPDATE a
SET
a.ID1 = c.TEMPID1 ,
a.Flag1 = 1
from
Application1 a,
(
SELECT ' + @Number + ' FROM Application1
GROUP BY ' + @Number + ' HAVING COUNT(*) > 1 )b,
#TEMPID c
where
a.'+ @Number + '= b.' +@Number +
' and a.'+@Number +' = c.'+@Number +
' AND a.system = ''' + @System +''''
EXEC (@SQL)
FETCH NEXT FROM CSR INTO @System, @Number
END
CLOSE CSR
DEALLOCATE CSR
But the problem is when it comes for the second time it can not take the raNumer as it is a different field name. getting error
Invalid object name '#TEMPID'.
how to fix the same logic in dynamic query?
thanks a lot
anamika
November 18, 2009 at 3:19 am
Hi,
Use the global temp table (##temp tables) in the dynamic executions.
November 18, 2009 at 3:44 am
thanks arun,
it works....
regards
anamika
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply