I am using SQL Server, and I have a table that looks like this: I want to update id to have a sequence of rows ordered by distance asc, then i asc I tried this but I am getting an error Incorrect syntax near "order" How to fix that? Answer You need to use ROW_NUMBER inside a derived table or

How to update with order in SQL Server?

I am using SQL Server, and I have a table that looks like this:

i    id   distance
-----------------
41   null    24
49   null    58
38   null    58
48   null    83
95   null    95
44   null    95
23   null    95

​x
 
i    id   distance-----------------41   null    2449   null    5838   null    5848   null    8395   null    9544   null    9523   null    95​

I want to update id to have a sequence of rows ordered by distance asc, then i asc

I tried this

update mytable
set @id = @id + 1
id = @id
order by distance, i

 
update mytableset @id = @id + 1id = @idorder by distance, i​

but I am getting an error

Incorrect syntax near “order”

How to fix that?

You need to use ROW_NUMBER inside a derived table or CTE.

e.g. as below

UPDATE T
SET    id = RN
FROM   (SELECT id,
               RN = ROW_NUMBER() OVER ( ORDER BY distance, i)
        FROM   mytable)T

 
UPDATE TSET    id = RNFROM   (SELECT id,               RN = ROW_NUMBER() OVER ( ORDER BY distance, i)        FROM   mytable)T ​