mysql - 我想从库存中扣除产品成分

Question

我有 3 个表订单、库存和配料。我希望订单(产品)在每次订购时自动从库存中扣除其成分。

ORDER_ITEMS

    +----------+--------+-----------------+
    | order_id |item_id |    item_name    |  
    +----------+--------+-----------------+
    |     1    |   1    |      coffee     |
    +----------+--------+-----------------+
    |     1    |   2    |      shake      | 
    +----------+--------+-----------------+

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    |
    +--------+-----------------+--------+

库存:

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

我试过了，但我似乎无法让它工作

UPDATE INVENTORY i, PRODUCT_INGREDIENT p 
SET i.amount = i.amount - p.amount
WHERE i.ingredient_id = p.ingredient_id

我希望我的库存在下订单后看起来像这样。

库存:

        +--------+-----------------+--------+
        |  id    | ingredient_id   | amount |
        +--------+-----------------+--------+
        |   1    |      123        |   80   |
        +--------+-----------------+--------+
        |   2    |      124        |   70   |
        +--------+-----------------+--------+
        |   3    |      125        |   85   |
        +--------+-----------------+--------+
        |   4    |      126        |   90   |
        +--------+-----------------+--------+

Answer 1

这是 MySQL update ... join 的语法:

update inventory i 
inner join product_ingredient p 
    on i.ingredient_name = p.ingredient_name 
set i.amount = i.amount - p.amount

注意:如果表 orders 参与查询，这会更有意义。此外，从这个表的外观来看，它似乎包含订单 items(因此将其命名为 order_items 会更好)；并且，它可能缺少一个名为 order_id 的列，该列唯一标识一个订单。因此，给定一个代表 order_id 的参数，您的查询将如下所示:

update inventory i 
inner join product_ingredient p 
    on i.ingredient_name = p.ingredient_name 
inner join order_items o 
    on o.item_id = p.item_id 
set i.amount = i.amount - p.amount
where o.oder_id = ?

---

更新

对于一次性更新，您可能需要聚合:

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