Mise à jour SQL avec row_number()

Question

Je veux mettre à jour ma colonne CODE_DEST avec un nombre incrémental. J'ai :

CODE_DEST   RS_NOM
null        qsdf
null        sdfqsdfqsdf
null        qsdfqsdf

Je voudrais le mettre à jour pour qu'il soit :

CODE_DEST   RS_NOM
1           qsdf
2           sdfqsdfqsdf
3           qsdfqsdf

J'ai essayé ce code :

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

Cela ne fonctionne pas à cause de la )

J'ai également essayé :

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

Mais cela ne fonctionne pas non plus à cause du syndicat.

Comment puis-je mettre à jour une colonne en utilisant la fonction ROW_NUMBER() dans SQL Server 2008 R2 ?

Answer 1

Une option supplémentaire

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 2

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

essayez ceci

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

Answer 3

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

Answer 4

Votre deuxième tentative a échoué principalement parce que vous avez nommé le CTE de la même manière que la table sous-jacente et que vous avez fait en sorte que le CTE ait l'air d'être une CTE récursif parce qu'il s'est essentiellement référencé lui-même. A CTE récursif doit avoir une structure spécifique qui nécessite l'utilisation de l'option UNION ALL opérateur de réglage.

Au lieu de cela, vous auriez pu simplement donner un nom différent au CTE et lui ajouter la colonne cible :

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 5

Il s'agit d'une version modifiée de la réponse de @Aleksandr Fedorenko qui ajoute une clause 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

En ajoutant une clause WHERE, j'ai constaté que les performances s'amélioraient massivement pour les mises à jour suivantes. Sql Server semble mettre à jour la ligne même si la valeur existe déjà et cela prend du temps, donc l'ajout de la clause where lui permet de sauter les lignes où la valeur n'a pas changé. Je dois dire que j'ai été étonné de la rapidité avec laquelle il a pu exécuter ma requête.

Avertissement : je ne suis pas un expert en bases de données, et j'utilise PARTITION BY pour ma clause, donc il se peut que les résultats ne soient pas exactement les mêmes pour cette requête. Pour moi, la colonne en question est la commande payée d'un client, donc la valeur ne change généralement pas une fois qu'elle est définie.

Assurez-vous également que vous disposez d'index, en particulier si vous avez une clause WHERE dans l'instruction SELECT. Un index filtré a bien fonctionné pour moi, car je filtrait sur la base des statuts de paiement.

---

Ma requête utilise PARTITION by

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 partie "IS NOT NULL" n'est pas nécessaire si la colonne n'est pas annulable.

---

Quand je dis que l'augmentation des performances a été massive, je veux dire qu'elle a été pratiquement instantanée lors de la mise à jour d'un petit nombre de lignes. Avec les bons index, j'ai pu réaliser une mise à jour qui prenait le même temps que la requête "interne" :

  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