如何在UPDATE子句中使用ROW_NUMBER（）？ [重复]

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

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

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

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