How to use ROW_NUMBER() in UPDATE clause?

Question

ROW_NUMBER() is only for used in the SELECT clause in MS SQL Server, but I want to use it for update like the following:

Update MyTab Set MyNo = 123 +  ROW_NUMBER() over (Order By ID)
Where a=b;

then I got Error like,

Windowed functions can only appear in the SELECT or ORDER BY clauses.

How to use ROW_NUMBER() in UPDATE clause?

Answer 1

You can use a CTE:

;WITH RowNbrs AS (
    SELECT  ID
            , ROW_NUMBER() OVER (ORDER BY ID) AS RowNbr
    FROM    MyTab
    WHERE   a = b
)
UPDATE  t 
SET     t.MyNo = 123 +  r.RowNbr
FROM    MyTab t
        JOIN RowNbrs r ON t.ID = r.ID;