How to update a column with IDs partitioned by another column?

Question

I have table ACCOUNT with following two columns

id,acct_num
1,123
2,123
3,456
4,121
5,123

I want to assign integer value in id column partitioned by acct_num

This is what I want -

id,acct_num
1,121
2,123
2,123
2,123
3,456

The IDs should be ordered by acct_num, identical for multiple occurrences of acct_num and unique within their column.
I have tried this -

update a
set a.id = row_number() over (partition by acct_num order by acct_num)
from account a

It is throwing error that windowed functions can be used only in SELECT. Can anybody help me?

Answer 1

You could use a Common Table Expression (CTE):

;WITH CTE AS(
    SELECT *,
        RN = DENSE_RANK() OVER(ORDER BY acct_num)
    FROM account
)
UPDATE CTE SET id = RN

SQL Fiddle