SQL Server 2019

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

select *
into t
from (values (1, 1, 0), (2, 1, 0), (3, 2, 0), (4, 2, 0), (4, 2, 3)) v(id, orderid, subid)
5 rows affected

select *
from t
order by id


id
orderid
subid


1
1
0

2
1
0

3
2
0

4
2
0

4
2
3


with toupdate as (
      select t.*,
             row_number() over (partition by orderid, subid order by id) as seqnum,
             max(subid) over (partition by orderid) as max_orderid
      from t
     )
update toupdate
    set subid = max_orderid + seqnum
    where subid = 0;
4 rows affected

select *
from t
order by id


id
orderid
subid


1
1
1

2
1
2

3
2
4

4
2
5

4
2
3