MariaDB 10.4

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table equiry_exchange_orders(id int not null auto_increment primary key, init float not null, avail float); insert into equiry_exchange_orders(init) values(100),(50),(25),(240),(300),(10);
set @need = 210;
update equiry_exchange_orders o
  join (
    select id, new_avail, @need := @need - new_avail
      from (
        select *, case when remain >= 0 then init else @need - prev_sum end new_avail
          from (
            select *, sum(init) over(order by id)-init prev_sum, @need-sum(init) over(order by id) remain
              from equiry_exchange_orders
        ) x 
      ) y
    where new_avail > 0 for update
  ) n on o.id=n.id
  set avail = new_avail
select * from equiry_exchange_orders;
select @need;


id
init
avail


1
100
100

2
50
50

3
25
25

4
240
35

5
300
null

6
10
null




@need


0