You can't do it in one statement. Try something like this:

SELECT ID INTO #Temp FROM YourTable WHERE Sort > 10

UPDATE YourTable SET Sort = Sort -1 WHERE ID IN (SELECT b.ID FROM #Temp AS
b)

SELECT ID FROM #Temp

--

Andrew J. Kelly
SQL Server MVP


[quoted text, click to view]

Hi,

You'll have to do a SELECT statement before (not after, since you are
changing the value of 'sort', which is also the one you use in the WHERE)
this one (with the same WHERE).

An UPDATE cannot return this.

--
Regards,
Kristofer Gafvert
http://www.ilopia.com - FAQ & Tutorials for Windows Server 2003, and SQL
Server 2000
Reply to newsgroup only. Remove NEWS if you must reply by email, but please
do not.


[quoted text, click to view]

I make a "UPDATE" query like this:

UPDATE table SET sort = sort - 1 WHERE sort > '10'

The problem is, I need to get a recordset back with the ids of the affected
rows. Can this be done?

Cheers,
LIN