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;