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.
Advertisement
Answer
You can use window functions:
x
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.