我有一个有效的查询，但我需要在其中添加一个where子句

这是一个有效的mysql语句

update inventory i
inner join (
select ingredient_id,sum(amount) amount
from product_ingredient p
group by ingredient_id
) p on i.ingredient_id = p.ingredient_id
set i.amount = i.amount - p.amount


这些是我数据库中的表

ORDER_ITEMS

+----------+--------+-----------------+------------+
| order_id |item_id | item_name | quantity |
+----------+--------+-----------------+------------+
| 1 | 1 | coffee | 2 |
| 1 | 2 | shake | 2 |
| 2 | 3 | icecream | 3 |
+----------+--------+-----------------+------------+


PRODUCT_INGREDIENT：

+--------+-----------------+--------+
|item_id | ingredient_id | amount |
+--------+-----------------+--------+
| 1 | 123 | 10 |
| 1 | 124 | 15 |
| 1 | 125 | 10 |
| 2 | 124 | 15 |
| 2 | 123 | 10 |
| 2 | 126 | 15 |
| 3 | 124 | 15 |
| 3 | 123 | 10 |
| 3 | 126 | 15 |
+--------+-----------------+--------+


库存：

+--------+-----------------+--------+
| id | ingredient_id | amount |
+--------+-----------------+--------+
| 1 | 123 | 100 |
| 2 | 124 | 100 |
| 3 | 125 | 100 |
| 4 | 126 | 100 |
+--------+-----------------+--------+


我需要为sql放置一个where语句，以便仅扣除order_id = 1
而不是全部都放在order_items表中

您可以将ORDER_ITEMS item_id与子查询p Ingredient_id结合起来

update inventory i
inner join (
select ingredient_id,sum(amount) amount
from product_ingredient p
group by ingredient_id
) p on i.ingredient_id = p.ingredient_id
INNER JOIN PRODUCT_INGREDIENT p1 on p1.ingredient_id = p.ingredient_id
INNER JOIN ORDER_ITEMS o ON o.item_id = p1.item_id
set i.amount = i.amount - p.amount
WHER o.order_id = 1


根据您的评论，您可能还需要item_id子查询

update inventory i
inner join (
select item_id,ingredient_id,sum(amount) amount
from product_ingredient p
group by item_id,ingredient_id
) p on i.ingredient_id = p.ingredient_id
INNER JOIN ORDER_ITEMS o ON o.item_id = p.item_id
set i.amount = i.amount - p.amount
WHERE o.order_id = 1


基于您在注释中提供的结果，可能是您需要在product_ingredient和ORDER_ITEMS之间为join_id = 1联接的子查询

UPDATE inventory i
INNER JOIN (
select p.ingredient_id,sum(p.amount) 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