SQL2005以上版本派生表更新

 

 

SQL2005以上版本可用

USE tempdb

go

SELECT   TOP 10 ID, Row= CAST (null AS INT ) INTO T FROM sysobjects

go

 

UPDATE ta

SET Row= row2

FROM

( SELECT *, row2=( SELECT COUNT ( 1) FROM T WHERE    ID>= a. ID ) FROM T a  ) AS Ta

 

SELECT * FROM T ORDER BY row ASC

 

/*

ID  Row

29  1

27  2

26  3

25  4

15  5

13  6

8   7

7   8

5   9

4   10

*/

DROP TABLE t