SQL Update with row number

Question

I want to update my column CODE DEST with an incremental number   I have   CODE DEST   RS NOM null        qsdf null        sdfqsdfqsdf null        qsdfqsdf   I would like to update it to be   CODE DEST   RS NOM 1           qsdf 2           sdfqsdfqsdf 3           qsdfqsdf   I have tried this code   UPDATE DESTINATAIRE TEMP SET CODE DEST   TheId  FROM  SELECT  Row Number     OVER  ORDER BY  RS NOM   AS TheId FROM DESTINATAIRE TEMP    This does not work because of the    I have also tried   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   But this also does not work because of union   How can I update a column using the ROW NUMBER   function in SQL Server 2008 R2

User · Answer

Your second attempt failed primarily because you named the CTE same as the underlying table and made the CTE look as if it was a recursive CTE, because it essentially referenced itself. A recursive CTE must have a specific structure which requires the use of the UNION ALL set operator.

Instead, you could just have given the CTE a different name as well as added the target column to it:

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

User · Answer

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

User · Answer

Simple and easy way to update the 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

User · Answer

DECLARE  id INT  SET  id   0  UPDATE DESTINATAIRE TEMP SET  id   CODE DEST    id   1  GO    try this  http   www mssqltips com sqlservertip 1467 populate-a-sql-server-column-with-a-sequential-number-not-using-an-identity

User · Answer

I did this for my situation and worked  WITH myUpdate  id  myRowNumber   AS        SELECT id  ROW NUMBER   over  order by ID  As myRowNumber     FROM AspNetUsers     WHERE  UserType  Customer       update AspNetUsers set EmployeeCode   FORMAT myRowNumber  00000     FROM myUpdate     left join AspNetUsers u on u Id myUpdate id

User · Answer

If table does not have relation  just copy all in new table with row number and remove old and rename new one with old one   Select   RowNum   ROW NUMBER   OVER ORDER BY SELECT NULL       INTO cdm dbo SALES2018 from    select   from SALE2018  as SalesSource

User · Answer

This is a modified version of   Aleksandr Fedorenko s answer adding a WHERE clause   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  lt  gt  x New CODE DEST AND x CODE DEST IS NOT NULL   By adding a WHERE clause I found the performance improved massively for subsequent updates  Sql Server seems to update the row even if the value already exists and it takes time to do so  so adding the where clause makes it just skip over rows where the value hasn t changed  I have to say I was astonished as to how fast it could run my query   Disclaimer  I m no DB expert  and I m using PARTITION BY for my clause so it may not be exactly the same results for this query  For me the column in question is a customer s paid order  so the value generally doesn t change once it is set   Also make sure you have indexes  especially if you have a WHERE clause on the SELECT statement  A filtered index worked great for me as I was filtering based on payment statuses     My query using 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  lt  gt  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   The  IS NOT NULL  part isn t required if the column isn t nullable     When I say the performance increase was massive I mean it was essentially instantaneous when updating a small number of rows  With the right indexes I was able to achieve an update that took the same amount of time as the  inner  query does by itself      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

User · Answer

One more option 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

[sql-server] SQL Update with row_number()

Examples related to sql-server

Examples related to sql-server-2008-r2

Examples related to sql-update