O'Reilly logo
  • Robert Richards thinks this is interesting:

The workaround is to write a query against T1 returning col1 and an expression based on the ROW_NUMBER function (call it rownum); define a table expression based on this query; finally, have an outer UPDATE statement against the CTE assign rownum to col1, like so:

WITH C AS
(
  SELECT col1, col2,
    ROW_NUMBER() OVER(ORDER BY col2) AS rownum
  FROM dbo.T1
)
UPDATE C
  SET col1 = rownum;

Query T1, and observe t...

From

Cover of Microsoft® SQL Server® 2012 High-Performance T-SQL Using Window Functions

Note

Need to run this - learned something new here.