Subtract a number from the rows until the number ends to zero

How can subtract a number from the rows to the end of the subtraction in MySQL with query update

If have table like this

Store Table
itemId  storeCode   qoh
1          1        20
1          2        30
1          3        40

and i want subtract “80” form qoh to get the output

itemId  storeCode   qoh
1          1        0
1          2        0
1          3        10

I tried by and not work

set @sum = 80;
Update store SET qoh =
(SELECT IF((@sum := @sum - qoh) > 0,0,qoh))
ORDER BY storeCode ASC;

What is the appropriate adjustment to do?

Answer

If you are running MySQL 8.0, you can do the computation with window functions.

The following select query gives you the expected results:

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

You can then turn this to an update:

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)

Demo on DB Fiddle:

itemId | storeCode | qoh
-----: | --------: | --:
     1 |         1 |   0
     1 |         2 |   0
     1 |         3 |  10
     1 |         4 |  50

I added an extra line at the end of your data to demonstrate that the queries leave the “following” qon untouched.