By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table ProjectDataGroupDetail(
id int identity(1,1),
some_grouping_col varchar(10),
ProjectDataGroupDetailID int,
ProjectDataGroup int
)
insert into ProjectDataGroupDetail (some_grouping_col)(
select 'A' union all
select 'A' union all
select 'A' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'B'
)
7 rows affected
merge into ProjectDataGroupDetail as t
using (
select id, row_number() over(partition by some_grouping_col order by id) as rn
from ProjectDataGroupDetail
) as s
on t.id = s.id
when matched then update
set
ProjectDataGroupDetailID = s.rn,
ProjectDataGroup = 1
;
7 rows affected
select *
from ProjectDataGroupDetail
| id | some_grouping_col | ProjectDataGroupDetailID | ProjectDataGroup |
|---|---|---|---|
| 1 | A | 1 | 1 |
| 2 | A | 2 | 1 |
| 3 | A | 3 | 1 |
| 4 | B | 1 | 1 |
| 5 | B | 2 | 1 |
| 6 | B | 3 | 1 |
| 7 | B | 4 | 1 |