SQL Server: UPDATE a table by using ORDER BY

Question

I would like to know if there is a way to use an order by clause when updating a table. I am updating a table and setting a consecutive number, that's why the order of the update is important. Using the following sql statement, I was able to solve it without using a cursor:

DECLARE @Number INT = 0  UPDATE Test SET @Number = Number = @Number +1 

now what I'd like to to do is an order by clause like so:

 DECLARE @Number INT = 0   UPDATE Test  SET @Number = Number = @Number +1  ORDER BY Test.Id DESC 

I've read: How to update and order by using ms sql The solutions to this question do not solve the ordering problem - they just filter the items on which the update is applied.

Take care, Martin

Answer 1

No.

Not a documented 100% supported way. There is an approach sometimes used for calculating running totals called "quirky update" that suggests that it might update in order of clustered index if certain conditions are met but as far as I know this relies completely on empirical observation rather than any guarantee.

But what version of SQL Server are you on? If SQL2005+ you might be able to do something with row_number and a CTE (You can update the CTE)

With cte As ( SELECT id,Number, ROW_NUMBER() OVER (ORDER BY id DESC) AS RN FROM Test ) UPDATE cte SET Number=RN 

Answer 2

You can not use ORDER BY as part of the UPDATE statement (you can use in sub-selects that are part of the update).

UPDATE Test  SET Number = rowNumber  FROM Test INNER JOIN  (SELECT ID, row_number() OVER (ORDER BY ID DESC) as rowNumber FROM Test) drRowNumbers ON drRowNumbers.ID = Test.ID