Update script for a column with (max+1 for the column group value) on basis of another id column

As shown in the image. I have a table with Id, OrderId, SubId. For which the some row values in SubId column is zero as shown in the left table in the image. I have to update the SubId rows which are having value 0 to Max(SubId)+1 with respect to OrderId with respect to Id column in ascending order. The result table will be as shown in the right image. Any friends please help me with a solution or some suggetions.

Answer

You can use window functions:

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;

Here is a db<>fiddle.