sql-server - example - sql server rank() over

¿Cómo usar ROW_NUMBER() en la cláusula UPDATE?

Question

Esta pregunta ya tiene una respuesta aquí:

• SQL Update con row_number () 7 respuestas

ROW_NUMBER() solo se usa en la cláusula SELECT en MS SQL Server , pero quiero usarlo para actualizaciones como las siguientes:

Update MyTab Set MyNo = 123 + ROW_NUMBER() over (Order By ID)
Where a=b;

entonces tengo un error como

Las funciones de ventana solo pueden aparecer en las cláusulas SELECT u ORDER BY.

¿Cómo usar ROW_NUMBER() en la cláusula UPDATE ?

Answer 1

Puedes usar un 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;

Answer 2

Yo usaría un 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

Puedes probar esto primero haciendo un

select * from myUpdate

Answer 3

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;

Resultados:

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