Created
July 7, 2014 19:20
-
-
Save gurjeet/d0daf64fed2c4a111aed to your computer and use it in GitHub Desktop.
Postgres' join-based UPDATE updates just once instead of multiple times
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* Item names */ | |
create table item (i_id int, i_name text); | |
/* Stock counts of items */ | |
create table stock (s_i_id int, s_quantity int); | |
/* All rows here represent one order; each row is its line items */ | |
create table order_line (ol_i_id int, ol_quantity int); | |
insert into item values(1, 'item 1'); | |
insert into item values(2, 'item 2'); | |
insert into item values(3, 'item 3'); | |
insert into stock values(1, 100); | |
insert into stock values(2, 100); | |
insert into stock values(3, 100); | |
insert into order_line values(1, 3); | |
insert into order_line values(2, 4); | |
insert into order_line values(3, 5); | |
insert into order_line values(2, 6); | |
select | |
ol.ol_i_id, | |
ol.ol_quantity, | |
i.I_NAME, | |
s.S_QUANTITY as s_quantity | |
from stock as s, | |
order_line as ol, | |
item as i | |
where s.S_I_ID = ol.ol_i_id | |
and i.I_ID = ol.ol_i_id; | |
ol_i_id | ol_quantity | i_name | s_quantity | |
---------+-------------+--------+------------ | |
1 | 3 | item 1 | 100 | |
2 | 4 | item 2 | 100 | |
2 | 6 | item 2 | 100 | |
3 | 5 | item 3 | 100 | |
(4 rows) | |
update stock as s | |
set S_QUANTITY = case | |
when s.S_QUANTITY >= ol.ol_quantity + 10 then | |
s.S_QUANTITY - ol.ol_quantity | |
else | |
s.S_QUANTITY - ol.ol_quantity + 91 | |
end | |
from order_line as ol, | |
item as i | |
where s.S_I_ID = ol.ol_i_id | |
and i.I_ID = ol.ol_i_id | |
returning | |
ol.ol_i_id, | |
ol.ol_quantity, | |
i.I_NAME, | |
s.S_QUANTITY as s_quantity; | |
-- I expect 4 rows in result, like above. | |
ol_i_id | ol_quantity | i_name | s_quantity | |
---------+-------------+--------+------------ | |
1 | 3 | item 1 | 97 | |
2 | 4 | item 2 | 96 | |
3 | 5 | item 3 | 95 | |
(3 rows) | |
select * from stock; | |
-- After the above update, I expect Item 2's quantity to be 90 (== 100 - 4 - 6) | |
s_i_id | s_quantity | |
--------+------------ | |
1 | 97 | |
2 | 96 | |
3 | 95 | |
(3 rows) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment