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?

Answer 1

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

Answer 2

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/

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

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

Answer 5

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 <> 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 <> 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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

In my case I added a new column and wanted to update it with the equevilat record number for the whole table

id  name       new_column (ORDER_NUM)
1   Ali        null
2   Ahmad      null
3   Mohammad   null
4   Nour       null
5   Hasan      null
6   Omar       null

I wrote this query to have the new column populated with the row number

UPDATE My_Table
SET My_Table.ORDER_NUM  = SubQuery.rowNumber
FROM (
         SELECT id ,ROW_NUMBER() OVER (ORDER BY [id]) AS rowNumber
         FROM My_Table
     ) SubQuery
INNER JOIN My_Table ON
        SubQuery.id = My_Table.id

after executing this query I had 1,2,3,... numbers in my new column