Update column using duplicate value counter

I want to calculate the duplicate value counter first, and then use it for another column to be updated.
How can I achieve that?

first, I have a table below:

id	another_id
1	null
1	null
1	null
2	null
2	null
2	null
2	null
…	…
N	null

and then calculating duplicate value count for id:

id	another_id	duplicate_cnt
1	null	1
1	null	2
1	null	3
2	null	1
2	null	2
2	null	3
2	null	4
…	…	…
N	null	X

Finally, using all duplicate_cnt to UPDATE another_id:

id	another_id	duplicate_cnt
1	1	1
1	2	2
1	3	3
2	1	1
2	2	2
2	3	3
2	4	4
…	…	…
N	X	X

Suggestions are greatly appreciated!

Answer

I would recommend just reconstructing the table:

create table temp_t as
    select t.*, row_number() over (partition by id order by id) as seqnum
    from t;

truncate table t;

insert into t (id, another_id)
    select id, seqnum
    from temp_t;

Note: Be very careful before using truncate table. Check the results first!