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?

Janssen

Post sample data and expected results, that is the best way to get a SQL answer with. Otherwise your ? makes no sense and will yield answers like this UPDATE myCol = myCol+1 FROM MyTable WHERE ID=@MyID – Bubaline

Answer 1

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

Janssen

This only works if values in column RS_NOM are unique, doesn't it? I doubt that this can be assumed. – Astridastride

There is actually no need for the JOIN here, as Andriy M demonstrates in their answer. – Morvin

Answer 2

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

Idzik

Is this a brilliant answer, or is it brilliant that SQL Server can update within a nested SELECT? I think both are.... – Sleeper

you can massively improve future update performance with a WHERE clause (see my answer based on this) – Transmittance

this worked like a charm! thanks :) i added a sortorder field in my child table and wanted to assign sequential sortorder per primary key group and it worked awesome. – Arian

Answer 3

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/

Typewritten

Only one of the above that worked for me in SQL Server 2012 – Arcade

Nice answer! Try: SET CODE_DEST = @id, @id = @id + 1 for readability. – Spectroheliograph

Neat, but this only works on a defined subset, so it has limited application with respect to scaling. If you want to update two sets of data based and iterate this value over both cases, this would require a loop. – Pusillanimous

Nice and simple – Latitudinarian

This only applies on one column and one value. – Supremacy

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

Ayacucho

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

Transmittance

@AleksandrFedorenko to be honest I was surprised it worked but glad it did :) the indexes were important that i created too – Transmittance

Thanks for this useful tip. Best regards. – Aftershock

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

Downwind

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

Negress

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

Exception

ORDER BY(SELECT NULL) - great. – Gin

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

Lacreshalacrimal

Answer 10

I update a temp table with the first occurrence of part where multiple parts can be associated with a sequence number. RowId=1 returns the first occurence which I join the tmp table and data using part and sequence number.

update #Tmp
set 
#Tmp.Amount=@Amount
from 
(SELECT Part, Row_Number()   OVER (ORDER BY [Part]) AS RowId FROM #Tmp
where Sequence_Num=@Sequence_Num
)data
where data.Part=#Tmp.Part
and data.RowId=1
and #Tmp.Sequence_Num=@Sequence_Num

Mathewmathews

Answer 11

I don't have a running ID in order to do what "Basheer AL-MOMANI" suggested. I did something like this: (joined my table on myself, just to get the Row Number)

update T1 set inID = T2.RN
from (select *, ROW_NUMBER() over (order by ID) RN from MyTable) T1
    inner join (select *, ROW_NUMBER() over (order by ID) RN from MyTable) T2 on T2.RN = T1.RN

Sample