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

Question

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 1

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.