By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table orders(id int,orderid varchar(10),variety varchar(50),weight float, quantity int, price int);
insert into orders values(1, 'J1', 'Native Chicken', 1.6, 10, 120);
insert into orders values(2, 'J2', 'Chicken Broiler', 2.3, 5, 100);
insert into orders values(3, 'J3', 'Duck' , 2.0, 2, 250);
insert into orders values(4, 'J4', 'Turkey' , 4.6, 4, 350);
create table Inventory (id int, variety varchar(50),weight float,quantity int, price int);
insert into Inventory values(1, 'Native Chicken', 1.6, 20, 120);
insert into Inventory values(2, 'Chicken Broiler', 2.3, 15, 100);
insert into Inventory values(3, 'Duck' , 1.6, 5, 250);
insert into Inventory values(4, 'Turkey' , 1.6, 9, 350);
insert into Inventory values(5, 'Native Chicken', 1.9, 20, 120);
insert into Inventory values(6, 'Chicken Broiler', 2.5, 15, 100);
insert into Inventory values(7, 'Duck' , 1.7, 5, 250);
insert into Inventory values(8, 'Turkey', 4.7, 9, 350);
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
update inventory i
set quantity= i.quantity - COALESCE(o.quantity, 0)
from orders o
where i.variety = o.variety AND i.weight = o.weight
2 rows affected
select * from inventory order by id
| id | variety | weight | quantity | price |
|---|---|---|---|---|
| 1 | Native Chicken | 1.6 | 10 | 120 |
| 2 | Chicken Broiler | 2.3 | 10 | 100 |
| 3 | Duck | 1.6 | 5 | 250 |
| 4 | Turkey | 1.6 | 9 | 350 |
| 5 | Native Chicken | 1.9 | 20 | 120 |
| 6 | Chicken Broiler | 2.5 | 15 | 100 |
| 7 | Duck | 1.7 | 5 | 250 |
| 8 | Turkey | 4.7 | 9 | 350 |