sql-server - over - row_number() sql server

Actualización de SQL con row_number()

Question

Quiero actualizar mi columna CODE_DEST con un número incremental. Yo tengo:

CODE_DEST   RS_NOM
null        qsdf
null        sdfqsdfqsdf
null        qsdfqsdf

Me gustaría actualizarlo para que sea:

CODE_DEST   RS_NOM
1           qsdf
2           sdfqsdfqsdf
3           qsdfqsdf

He intentado este código:

UPDATE DESTINATAIRE_TEMP
SET CODE_DEST = TheId 
FROM (SELECT  Row_Number()   OVER (ORDER BY [RS_NOM]) AS TheId FROM DESTINATAIRE_TEMP)

Esto no funciona debido a )

También he intentado:

WITH DESTINATAIRE_TEMP AS
  (
    SELECT 
    ROW_NUMBER() OVER (ORDER BY [RS_NOM] DESC) AS RN
    FROM DESTINATAIRE_TEMP
  )
UPDATE DESTINATAIRE_TEMP SET CODE_DEST=RN

Pero esto tampoco funciona debido a la unión.

¿Cómo puedo actualizar una columna usando la función ROW_NUMBER() en SQL Server 2008 R2?

Answer 1

Esta es una versión modificada de la respuesta de @Aleksandr Fedorenko agregando una cláusula WHERE:

UPDATE x
SET x.CODE_DEST = x.New_CODE_DEST
FROM (
      SELECT CODE_DEST, ROW_NUMBER() OVER (ORDER BY [RS_NOM]) AS New_CODE_DEST
      FROM DESTINATAIRE_TEMP
      ) x
WHERE x.CODE_DEST <> x.New_CODE_DEST AND x.CODE_DEST IS NOT NULL

Al agregar una cláusula WHERE, encontré que el rendimiento mejoró de forma masiva para las actualizaciones posteriores. El servidor SQL parece actualizar la fila incluso si el valor ya existe y lleva tiempo hacerlo, por lo que al agregar la cláusula where hace que salte las filas donde el valor no ha cambiado. Debo decir que me sorprendió lo rápido que podría ejecutar mi consulta.

Descargo de responsabilidad: no soy experto en DB, y estoy usando PARTITION BY para mi cláusula, por lo que puede que no sean exactamente los mismos resultados para esta consulta. Para mí, la columna en cuestión es la orden paga de un cliente, por lo que el valor generalmente no cambia una vez que se configura.

También asegúrese de tener índices, especialmente si tiene una cláusula WHERE en la instrucción SELECT. Un índice filtrado me funcionó muy bien ya que estaba filtrando en función de los estados de pago.

Mi consulta usando PARTITION por

UPDATE  UpdateTarget
SET     PaidOrderIndex = New_PaidOrderIndex
FROM
(
    SELECT  PaidOrderIndex, SimpleMembershipUserName, ROW_NUMBER() OVER(PARTITION BY SimpleMembershipUserName ORDER BY OrderId) AS New_PaidOrderIndex
    FROM    [Order]
    WHERE   PaymentStatusTypeId in (2,3,6) and SimpleMembershipUserName is not null
) AS UpdateTarget

WHERE UpdateTarget.PaidOrderIndex <> UpdateTarget.New_PaidOrderIndex AND UpdateTarget.PaidOrderIndex IS NOT NULL

-- test to ''break'' some of the rows, and then run the UPDATE again
update [order] set PaidOrderIndex = 2 where PaidOrderIndex=3

La parte ''IS NOT NULL'' no es obligatoria si la columna no puede contener nulos.

Cuando digo que el aumento en el rendimiento fue masivo quiero decir que fue esencialmente instantáneo al actualizar un número pequeño de filas. Con los índices correctos pude lograr una actualización que tomó el mismo tiempo que la consulta ''interna'' por sí misma:

  SELECT  PaidOrderIndex, SimpleMembershipUserName, ROW_NUMBER() OVER(PARTITION BY SimpleMembershipUserName ORDER BY OrderId) AS New_PaidOrderIndex
    FROM    [Order]
    WHERE   PaymentStatusTypeId in (2,3,6) and SimpleMembershipUserName is not null

Answer 2

Manera simple y fácil de actualizar el cursor

UPDATE Cursor
SET Cursor.CODE = Cursor.New_CODE
FROM (
  SELECT CODE, ROW_NUMBER() OVER (ORDER BY [CODE]) AS New_CODE
  FROM Table Where CODE BETWEEN 1000 AND 1999
  ) Cursor

Answer 3

Su segundo intento falló principalmente porque usted nombró el CTE como la tabla subyacente e hizo que el CTE se viera como si fuera un CTE recursivo , porque esencialmente se hacía referencia a sí mismo. Un CTE recursivo debe tener una estructura específica que requiera el uso del operador de conjunto UNION ALL .

En cambio, podría haberle dado al CTE un nombre diferente y haberle agregado la columna de destino:

With SomeName As
(
SELECT 
CODE_DEST,
ROW_NUMBER() OVER (ORDER BY [RS_NOM] DESC) AS RN
FROM DESTINATAIRE_TEMP
)
UPDATE SomeName SET CODE_DEST=RN

Answer 4

Una opción más

UPDATE x
SET x.CODE_DEST = x.New_CODE_DEST
FROM (
      SELECT CODE_DEST, ROW_NUMBER() OVER (ORDER BY [RS_NOM]) AS New_CODE_DEST
      FROM DESTINATAIRE_TEMP
      ) x

Answer 5

DECLARE @id INT 
SET @id = 0 
UPDATE DESTINATAIRE_TEMP
SET @id = CODE_DEST = @id + 1 
GO 

prueba esto

http://www.mssqltips.com/sqlservertip/1467/populate-a-sql-server-column-with-a-sequential-number-not-using-an-identity/

Answer 6

With UpdateData  As
(
SELECT RS_NOM,
ROW_NUMBER() OVER (ORDER BY [RS_NOM] DESC) AS RN
FROM DESTINATAIRE_TEMP
)
UPDATE DESTINATAIRE_TEMP SET CODE_DEST = RN
FROM DESTINATAIRE_TEMP
INNER JOIN UpdateData ON DESTINATAIRE_TEMP.RS_NOM = UpdateData.RS_NOM