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

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

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 CTE.

e.g. as below

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