Skip to content

Instantly share code, notes, and snippets.

@gurjeet
Created July 7, 2014 19:20
Show Gist options
  • Save gurjeet/d0daf64fed2c4a111aed to your computer and use it in GitHub Desktop.
Save gurjeet/d0daf64fed2c4a111aed to your computer and use it in GitHub Desktop.
Postgres' join-based UPDATE updates just once instead of multiple times
/* 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