SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table CPDG(asset_id int, cnt int);
insert into CPDG(asset_id) values
(1), (1), (1), (2), (2), (3);
6 rows affected

with cte as (
  select asset_id, cnt, 
    ROW_NUMBER() OVER (PARTITION BY asset_id ORDER BY asset_id) as rn
  from CPDG
)
update cte 
set cnt = rn
6 rows affected

select * from CPDG


asset_id
cnt


1
1

1
2

1
3

2
1

2
2

3
1