CODEWITHSUNDEEP
databasepostgresqlconditional-statements
SlimenTN
SlimenTN

Reputation: 3564

Postgresql, update multiple rows and stop update with condition

I have table invoice which looks like this:
enter image description here

And I have 2000 as amount of money that I want to reduce it from these invoices one by one and when the amount of money is over I want the query to stop like this:
invoice with id = 2 --> 1500 < 2000 --> so the paid_money becomes 0
and the rest of amount of money is: 2000 - 1500 = 500 (still > 0 so we continue to the next invoice)
invoice with id = 1 -->1500.2 > 500 --> so the paid_money becomes 1500.2 - 500 = 1000.2
and the rest of the amount of money = 0 (this is the condition where I want to stop the update query)
The order of update is ORDER BY id DESC.
And thnx for your help.

Upvotes: 1

Views: 106

Answers (2)

Christian
Christian

Reputation: 7320

There are so many ways of doing that. Here is one with a single sql (without creating any function or procedure):

-- creates the invoice table for testing
drop table if exists invoice;
create table invoice (id integer primary key, value numeric, paid_value numeric, money_remaining numeric);
-- inserts invoices data
insert into invoice  (id, value, paid_value, money_remaining) values (1, 500, 0, 0), (2, 700, 0, 0), (3, 1000, 0, 0); 

with 
-- computes the total of money necessary to pay all invoices including the current one
-- acc_value = sum of all invoices where id is less or equals to the current invoice
a as 
(
select  i.id, i.value, (select sum(value) from invoice i1 where i1.id <= i.id) acc_value
from    invoice i
),

-- computes the total of money spent
b as 
(
select  *, (2000 - acc_value) as money_remaining
         , case when (2000 - acc_value) >= 0 then value else 0 end as paid_value
from    a
)

-- updates each invoice with it's computed values
update invoice i
   set paid_value = b.paid_value,
       money_remaining = case when b.money_remaining < 0 then (i.value + b.money_remaining) else b.money_remaining end
from b
where i.id = b.id;

-- shows the result
select * from invoice;

Upvotes: 0

Gabriel&#39;s Messanger
Gabriel&#39;s Messanger

Reputation: 3298

First of all I doubt it could be done with single SQL command because of need of specific order. You should write PL/pgSQL procedure to iterate rows in order, do the logic and modification per row and terminate execution in right moment.

Upvotes: 1

Related Questions