Postgresql, update multiple rows and stop update with condition

Question

I have table invoice which looks like this:

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.

Gabriel's Messanger · Accepted Answer · 2015-10-09 07:46:06Z

1

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.

answered Oct 9, 2015 at 7:46

Gabriel's Messanger

3,36822 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SlimenTN Over a year ago

Thank you, I wanted to know if there is an easy way to do this but you make it clear to me :)

Craig Ringer Over a year ago

I suspect it can be done with a recursive CTE that determines which rows to allocate what funds to, then a separate CTE term that actually applies those decisions.

Christian · Accepted Answer · 2015-10-09 13:51:23Z

0

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;

answered Oct 9, 2015 at 13:51

Christian

7,3509 gold badges58 silver badges81 bronze badges

1 Comment

SlimenTN Over a year ago

Very nice, thnx for your help :)

Collectives™ on Stack Overflow

Postgresql, update multiple rows and stop update with condition

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related