SQL Update with row_number()

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?

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

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/

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

SQL Update with row_number()

Related

Recent Posts