Using ROW_NUMBER() in SQL Server update statement

 No Responses »
Sep 182014
 

You can’t use ROW_NUMBER() in an update statement in SQL Server, so:

UPDATE TheTable
SET    TheColumn = ROW_NUMBER();

won’t work. But sometimes that’s just what you want. This will do the trick:

UPDATE  Target
SET     TheColumn = RowNum
FROM
(
    SELECT  t.TheColumn, ROW_NUMBER() OVER(ORDER BY t.ID) AS RowNum
    FROM    TheTable t
	WHERE    ...
) AS Target;