SQL Server: UPDATE a table by using ORDER BY

Question

Hi,

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: http://stackoverflow.com/questions/655010/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

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

Answer 2

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 3

The row_number() function would be the best approach to this problem.

UPDATE T
    SET T.Number = R.rowNum
    FROM Test T
    JOIN (
        SELECT T2.id,row_number() over (order by T2.Id desc) rowNum from Test T2
    ) R on T.id=R.id 

Answer 4

Edit

Following solution could have problems with clustered indexes involved as mentioned here. Thanks to Martin for pointing this out.

The answer is kept to educate those (like me) who don't know all side-effects or ins and outs of SQL Server.

---

Expanding on the answer gaven by Quassnoi in your link, following works

DECLARE @Test TABLE (Number INTEGER, AText VARCHAR(2), ID INTEGER)
DECLARE @Number INT

INSERT INTO @Test VALUES (1, 'A', 1)
INSERT INTO @Test VALUES (2, 'B', 2)
INSERT INTO @Test VALUES (1, 'E', 5)
INSERT INTO @Test VALUES (3, 'C', 3)
INSERT INTO @Test VALUES (2, 'D', 4)

SET @Number = 0

---

;WITH q AS (
    SELECT  TOP 1000000 *
    FROM    @Test
    ORDER BY
            ID
)            
UPDATE  q
SET     @Number = Number = @Number + 1