By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE store(
itemId INT NOT NULL
,storeCode INTEGER NOT NULL
,qoh INTEGER NOT NULL
);
INSERT INTO store(itemId,storeCode,qoh) VALUES (1,1,20);
INSERT INTO store(itemId,storeCode,qoh) VALUES (1,2,30);
INSERT INTO store(itemId,storeCode,qoh) VALUES (1,3,40);
INSERT INTO store(itemId,storeCode,qoh) VALUES (1,4,50);
select
s.*,
case when sum(qoh) over(partition by itemid order by storecode) - qoh >= 80
then qoh
else greatest(
sum(qoh) over(partition by itemid order by storecode) - 80,
0
)
end new_qoh
from store s
| itemId | storeCode | qoh | new_qoh |
|---|---|---|---|
| 1 | 1 | 20 | 0 |
| 1 | 2 | 30 | 0 |
| 1 | 3 | 40 | 10 |
| 1 | 4 | 50 | 50 |
update store s
inner join (
select
s.*,
sum(qoh) over(partition by itemid order by storecode) sum_qoh
from store s
) n
on n.itemid = s.itemid
and n.storecode = s.storecode
and n.sum_qoh - s.qoh < 80
set s.qoh = greatest(n.sum_qoh - 80, 0)
Rows matched: 3 Changed: 3 Warnings: 0
select * from store order by itemid, storecode
| itemId | storeCode | qoh |
|---|---|---|
| 1 | 1 | 0 |
| 1 | 2 | 0 |
| 1 | 3 | 10 |
| 1 | 4 | 50 |