参见英文答案 >
SQL Update with row_number()
ROW_NUMBER()仅用于MS sql Server中的SELECT子句，但是我想将其用于如下更新：
ROW_NUMBER()仅用于MS sql Server中的SELECT子句，但是我想将其用于如下更新：
Update MyTab Set MyNo = 123 + ROW_NUMBER() over (Order By ID) Where a=b;
那我就得错了，
Windowed functions can only appear in the SELECT or ORDER BY clauses.
如何在UPDATE子句中使用ROW_NUMBER()？
解决方法
DECLARE @MyTable TABLE
(
ID INT IDENTITY(2,2) PRIMARY KEY,MyNum INT,ColA INT,ColB INT
);
INSERT @MyTable (ColA,ColB)
SELECT 11,11 UNION ALL
SELECT 22,22 UNION ALL
SELECT NULL,NULL UNION ALL
SELECT 33,NULL UNION ALL
SELECT NULL,44 UNION ALL
SELECT 55,66;
UPDATE UpdateTarget
SET MyNum = RowNum
FROM
(
SELECT x.MyNum,ROW_NUMBER() OVER(ORDER BY x.ID) AS RowNum
FROM @MyTable x
WHERE x.ColA = x.ColB
) AS UpdateTarget;
SELECT * FROM @MyTable;
结果：
ID MyNum ColA ColB ----------- ----------- ----------- ----------- 2 1 11 11 4 2 22 22 6 NULL NULL NULL 8 NULL 33 NULL 10 NULL NULL 44 12 NULL 55 66
