sql - Mysql: How I can Do this by update mysql query? -

alsalamo alikom

i have value price ex: 1000 , have rows in table ex:

id price
1 100
2 200
3 300
4 600

mysqli_query($connect,"update table set price = 1000-price price >0 "); // wrong  

and want after update mysql

id price
1 0
2 0
3 0
4 200

1 0
becouse 100<1000
price =1000-100=900
column=0
2 0
becouse 200<900
price =900-200=700
column=0
3 0
becouse 300<700
price =700-300=400
column=0
4 200
price =400-400=0
column=200 becouse 600>400

drop table if exists t; create table t(id int, price int); insert t values  (1, 100),  (2, 200), (3, 300) , (4, 600);  select id, if(price < running_total, 0,running_total) price ( select id,price,         if(@running_total > price ,@running_total := @running_total - price, price - @running_total) running_total    (select @running_total:=1000) rt,t ) s  

result

+------+-------+ | id   | price | +------+-------+ |    1 |     0 | |    2 |     0 | |    3 |     0 | |    4 |   200 | +------+-------+