MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table order_items( order_id int, item_id int, item_name varchar(15), quantity int);

insert into order_items values(1,1,'coffee',2),
                              (2,2,'shake',2),
                              (2,3,'icecream',3);
create table product_ingredient ( item_id int, ingredient_id int, amount int);

insert into product_ingredient values(1,123,10),(1,124,15),
                                     (2,123,10),(2,124,15),(2,125,10),(2,126,15),
                                     (3,124,15),(3,123,10),(2,126,15);
create table inventory( id int, ingredient_id int, amount int);

insert into inventory values(1,123,80),
                            (2,124,70),
                            (3,125,100),
                            (4,126,100);
                            
  SELECT p.ingredient_id, sum(p.amount*o.quantity) amount
  FROM product_ingredient p 
  INNER JOIN  order_items o on o.item_id = p.item_id
  WHERE o.order_id = 1
  GROUP BY p.ingredient_id


ingredient_id
amount


123
20

124
30


UPDATE inventory i 
 INNER JOIN (
  SELECT p.ingredient_id, sum(p.amount*o.quantity) amount
  FROM product_ingredient p 
  INNER JOIN  order_items o on o.item_id = p.item_id
  WHERE o.order_id = 1  
  GROUP BY p.ingredient_id
) p ON  i.ingredient_id = p.ingredient_id 
SET i.amount = i.amount + p.amount 
SELECT * FROM inventory


id
ingredient_id
amount


1
123
100

2
124
100

3
125
100

4
126
100