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