ROW_NUMBER()
只能在 MS SQL Server
的 SELECT
子句中使用,但我想像下面这样在更新中使用它:
Update MyTab Set MyNo = 123 + ROW_NUMBER() over (Order By ID)
Where a=b;
然后我遇到了如下错误:
窗口函数只能出现在SELECT或ORDER BY子句中。
如何在UPDATE
子句中使用ROW_NUMBER()
?
ROW_NUMBER()
只能在 MS SQL Server
的 SELECT
子句中使用,但我想像下面这样在更新中使用它:
Update MyTab Set MyNo = 123 + ROW_NUMBER() over (Order By ID)
Where a=b;
然后我遇到了如下错误:
窗口函数只能出现在SELECT或ORDER BY子句中。
如何在UPDATE
子句中使用ROW_NUMBER()
?
您可以使用公共表表达式(CTE):
;WITH RowNbrs AS (
SELECT ID
, ROW_NUMBER() OVER (ORDER BY ID) AS RowNbr
FROM MyTab
WHERE a = b
)
UPDATE t
SET t.MyNo = 123 + r.RowNbr
FROM MyTab t
JOIN RowNbrs r ON t.ID = r.ID;
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
WITH myUpdate ( myRowNumber )
AS
(
SELECT ROW_NUMBER() over (order by ID) As myRowNumber
FROM MyTab
WHERE a = b
)
update MyTab
set MyNo = 123 + myRowNumber
FROM myUpdate
select * from myUpdate