If two tables: A product containing information about the goods; and an Order table containing product codes and their quantities. How to subtract one from the other? What should the query look like? enter image description here

That's what I came up with, but it doesn't work. Unknown Orders_has_Goods.Goods_id_goods column in a where clause

update mall.Goods set Goods.amount = Goods.amount - Orders_has_Goods.quantity where Goods.id_goods = Orders_has_Goods.Goods_id_goods;
  • You are interested in an update request, or select? zhzhoynit them yes deduct complexity what? - teran
  • The basis is the update. If I make a join, will the results be entered in separate tables? - Dmitry Moskovchenko
  • so you only need to reduce a warehouse, or not? - teran
  • Yes, you need to subtract the numbers in the Order from the Products. - Dmitry Moskovchenko
  • You have in the table of orders, then there is not one order, I guess. need a request for a particular, or sum up all? - teran

2 answers 2

It is possible through the stored procedure

 CREATE PROCEDURE `my`() begin for item in (select order.id_goods, sum(order.count) as count from order group by order.id_goods) loop update goods set goods.count = goods.count - item.count; end loop; end;

    Solved a problem thanks to teran comment

     create procedure 'OrderFill' (IN count INT) begin set sql_safe_updates = 0; //в процедуре было несколько команд помимо той, что ниже, но в ответе они не уместны update Goods g, Orders_has_Goods o set g.amount = g.amount - o.quantity where g.id_goods = o.Goods_id_goods and o.Orders_id_orders = count; end