This question already has an answer here:
这个问题在这里已有答案:
- SQL Update with row_number() 6 answers
- SQL update with row_number()6个答案
ROW_NUMBER()
is only for used in the SELECT
clause in MS SQL Server
, but I want to use it for update like the following:
ROW_NUMBER()仅用于MS SQL Server中的SELECT子句,但我想将其用于更新,如下所示:
Update MyTab Set MyNo = 123 + ROW_NUMBER() over (Order By ID)
Where a=b;
then I got Error like,
然后我得到了错误,
Windowed functions can only appear in the SELECT or ORDER BY clauses.
窗口函数只能出现在SELECT或ORDER BY子句中。
How to use ROW_NUMBER()
in UPDATE
clause?
如何在UPDATE子句中使用ROW_NUMBER()?
3 个解决方案
#1
23
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;
Results:
结果:
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
#2
45
You can use a CTE:
您可以使用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;
#3
4
I would use a CTE
我会用CTE
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
You can test this first though by doing a
你可以先测试一下这个
select * from myUpdate
#1
23
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;
Results:
结果:
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
#2
45
You can use a CTE:
您可以使用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;
#3
4
I would use a CTE
我会用CTE
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
You can test this first though by doing a
你可以先测试一下这个
select * from myUpdate