CODEWITHSUNDEEP
mysqlsql
Lay_01
Lay_01

Reputation: 11

Update field of first row where it's >0 until it reaches 0 than subtract from next row?

Is there a way to update the first row field that it's >0 and subtract from it until it reaches 0 then move on to the next row and keep subtracting from it?

Table:

Column Amount
First 0
Second 5
Third 5

Amount to subtract = 9

Result:

Column Amount
First 0
Second 0
Third 1

Upvotes: 1

Views: 370

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Just use a cumulative sum:

select t.*,
       (case when 9 >= running_amount then 0
             when 9 >= running_amount - amount then running_amount - 9
             else amount
        end) as remainder
from (select t.*,
             sum(amount) over (order by column) as running_amount
      from t
     ) t;

Here is a db<>fiddle.

Note: This assumes that the first column actually represents the ordering of the rows.

Upvotes: 0

James
James

Reputation: 3015

Even the solution by @Kazi is perfect, maybe this is a simplified version of the query, using cols as rownumbers

with recursive
data as (
  select 1 as col, 0 as amount union all
  select 2,5 union all
  select 3,5
),
cte as (
  -- anchor member
  select
    col, 
    amount, 
    9 as substract
  from data
  where col = 1
  union all
  -- recursive member
  select 
    data.col,
    case when (data.amount - cte.substract) < 0 then 0 else data.amount - cte.substract end,
    case when (cte.substract - data.amount) < 0 then 0 else cte.substract - data.amount end
  from data
    join cte
      on data.col = cte.col + 1
)
select * from cte

you can test on this dbfiddle.

This is the only way to solve this kind of queries using "pure" sql, but the performance of these queries working with big amounts of data is not usually good.

Upvotes: 0

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

You can use recursion to get your job done. But it's will only run on MySQL 8.0 and higher.

 create table mytable (Columns varchar(50),Amount int);
 insert into mytable values('First',    0);
 insert into mytable values('Second',   5);
 insert into mytable values('Third',    5);

Query:

 with recursive
 cte as
 (
   select Columns,amount,row_number()over(order by Columns) rn from mytable
 )
 , cte2 as
 (
  select Columns, (case when amount0 then (case when amount9 then amount-9 else 0 end) else 0 end)amount,
  (case when amount0 then (case when amount9 then 0 else 9-amount end) else 9 end)remainingamount,rn
  from cte where rn=1
  union all
  select cte.Columns,(case when cte.amount0 then (case when cte.amountremainingamount then cte.amount-remainingamount else 0 end)else 0 end)amount
  ,(case when cte.amount0 then (case when cte.amountremainingamount then 0 else remainingamount-cte.amount end) else 0 end)remainingamount ,cte.rn
  from  cte inner join cte2 on cte2.rn=cte.rn-1
 )
 select columns, amount from cte2

Output:

columns amount
First 0
Second 0
Third 1

db<fiddle here

Upvotes: 1

Related Questions