combining UPDATE and TOP/ORDER BY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

combining UPDATE and TOP/ORDER BY

Author

Topic

Cornelius19
Starting Member

30 Posts

Posted - 2009-05-22 : 13:01:44

Hi,

Is there a way to combine UPDATE and TOP? I would like to update a column of records that have the top 1000 value in an other column. Basically, my query should be the combination of the following two:

UPDATE tmp
SET Col01 = 'top1000'

and

SELECT TOP 1000 Col01
FROM tmp
ORDER BY Col02 DESC

Is there any way to combine this two?

Thanks,

Cornelius

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-22 : 13:09:35

Use IN or EXISTS

UPDATE Table1
SET Col1 = xyz
WHERE Col1 IN (SELECT TOP 1000 Col1 FROM Table1 ORDER BY Col2)

UPDATE t1
SET t1.Col1 = xyz
FROM Table1 AS t1
WHERE EXISTS (SELECT TOP 1000 x.Col1 FROM Table1 AS x WHERE x.Col1 = t1.Col1 ORDER BY Col2)

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 13:35:38

also join method


UPDATE t1
SET t1.Col1 = xyz
FROM Table1 t1
JOIN (SELECT TOP 1000 Col1 FROM Table1 ORDER BY Col2)t2
ON t2.Col1=t1.Col1

Cornelius19
Starting Member

30 Posts

Posted - 2009-05-22 : 18:22:00

Thank you,

Cornelius

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-23 : 11:32:08

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources