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?

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

SQL Update with row_number()

Solution

Problem