Update with CTE and row-numbers as sequence or TSQL update query with ROW_NUMBER()

Question

I have a table named Site with columns Name, SiteId and Sequence. I would like to fill the Sequence field with the rownumber. I've tried the following query, but it just doesn't update the records:

WITH RowNumbers AS 
(
    select   SiteId,
             RowNum = row_number() OVER ( order by SiteId )
    from     [Site] 
)
UPDATE  s
SET     s.[Sequence] = r.RowNum
FROM    [Site] as s INNER JOIN RowNumbers as r ON s.SiteId = r.Row

What am I doing wrong?

Answer 1

You can update the CTE directly...

WITH RowNumbers AS 
(
    select   *,
             RowNum = row_number() OVER ( order by SiteId )
    from     [Site] 
)
UPDATE  RowNumbers
SET     [Sequence] = RowNum

This works in the same way as an updatable view. I added * to ensure the the updated field comes through, and then updated it directly.