SQL query to update rows

Question

I have data in a table in following format

ID  DocNumber   RevOrder
1   DOC-001     NULL
2   DOC-001     NULL
3   DOC-001     NULL
4   DOC-002     NULL
5   D0C-002     NULL
6   D0C-003     NULL

I need to update the RevOrder column in to following format

ID  DocNumber   RevOrder
1   DOC-001     3
2   DOC-001     2
3   DOC-001     1
4   DOC-002     2
5   D0C-002     1
6   D0C-003     1

Logic is: DocNumber can be duplicated and the DocNumber with the max ID gets the RevOrder = 1, next get the RevOrder = 2 etc... how can I achieve the above scenario?

Answer 1

Use this UPDATE statement based on a ROW_NUMBER() over a partition:

;WITH UpdateData AS
(
    SELECT 
       ID, DocNumber,
       ROW_NUMBER() OVER(PARTITION BY DocNumber ORDER BY ID DESC) AS 'RowNum' 
    FROM dbo.YourTable
)
UPDATE dbo.YourTable
SET RevOrder = RowNum
FROM dbo.YourTable t
INNER JOIN UpdateData ud ON t.ID = ud.ID