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...
- 1. SQL Windowing
- from Microsoft® SQL Server® 2012 High-Performance T-SQL Using Window Functions
- Publisher: Microsoft Press
- Released: April 2012
Need to run this - learned something new here.
Share this highlighthttp://www.safaribooksonline.com/a/microsoft-sql-server/4141178/