is this involves a co-related subquery

Question

Post reply

is this involves a co-related subquery

Anamika

SSCertifiable

Points: 6202
More actions
November 17, 2009 at 4:25 am

#139453

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

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150468 More actions · Answer 1

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
All articles available on SQL.kiwi
@SQL_Kiwi

justanewone Ten Centuries Points: 1398 More actions · Answer 2

UPDATE [Table]

SET flag = 1

WHERE saNumber IN(SELECT saNumber FROM [Table] GROUP BY saNumber HAVING COUNT(*) > 1)

Dave

Anamika SSCertifiable Points: 6202 More actions · Answer 3

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

arun.sas SSChampion Points: 11831 More actions · Answer 4

Hi,

Use the RANK.

Ref:

http://www.sqlservercentral.com/Forums/Topic819194-145-1.aspx

Paul White SSC Guru Points: 150468 More actions · Answer 5

If you need sequential numbers, use DENSE_RANK.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Anamika SSCertifiable Points: 6202 More actions · Answer 6

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

arun.sas SSChampion Points: 11831 More actions · Answer 7

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)

Anamika SSCertifiable Points: 6202 More actions · Answer 8

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

arun.sas SSChampion Points: 11831 More actions · Answer 9

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)

Anamika SSCertifiable Points: 6202 More actions · Answer 10

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

arun.sas SSChampion Points: 11831 More actions · Answer 11

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

Anamika SSCertifiable Points: 6202 More actions · Answer 12

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 @SystemAS NVARCHAR(50)

,@NumberAS NVARCHAR(50)

,@SQLAS VARCHAR(MAX)

,@TEMPSQLAS 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

arun.sas SSChampion Points: 11831 More actions · Answer 13

Hi,

Use the global temp table (##temp tables) in the dynamic executions.

Anamika SSCertifiable Points: 6202 More actions · Answer 14

Anamika

SSCertifiable

Points: 6202

November 18, 2009 at 3:44 am

#1080780

thanks arun,

it works....

regards

anamika