Trying to do basic subtraction from a database table

I’m trying to subtract data based on user input, for example:

**id  |  quantity
  1   |  50
  2   |  30
  3   |  60**

UPDATE table SET quantity=quantity – * some input value*

This is easy if I’m subtracting field by field, where id=1…2…3, but I want to subtract values automatically when I input quantity, so if input is 60, and there is 50 in the first field, remainder would be subtracted from next field, and so on, like this:

$input = 60; Now first quantity will be 50-60=0, mod=10, second 30-10=20.

**id  |  quantity
  1   |  0
  2   |  20
  3   |  60**

Can someone help me? I tried everything I could remember. Thanks!

Answer

You can calculate the new values using:

select t.*,
       (case when running_quantity - quantity >= 60 then 0
             when running_quantity > 60 then running_quantity - quantity
             else quantity
        end) as new_quantity
from (select t.*, sum(quantity) over (order by id) as running_quantity
      from t
     ) t

You can incorporate this into an update:

update t join
       (select t.*,
             (case when running_quantity - quantity >= 60 then 0
                   when running_quantity > 60 then running_quantity - quantity
                   else quantity
              end) as new_quantity
       from (select t.*, sum(quantity) over (order by id) as running_quantity
             from t
            ) t
       ) tt
       on tt.id = t.id
    set quantity = new_quantity
    where new_quantity <> quantity;