1

I have 3 tables inprod, outprod and inventory, they all have Quantity and ProdName in common. I want to subtract the value of Quantity column from inprod and outprod then save it to inventory. Right now I'm still reading for ways on how to do it like Join queries but I want something that will be the best solution for this, simple and clean.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Aldrin Roxas
  • 7
  • 4

2 Answers2

0

Something similar to

UPDATE inventory a
JOIN inprod  b on a.Quantity=b.Quantity and a.ProdName=b.ProdName
JOIN outprod c on a.Quantity=c.Quantity and a.ProdName=c.ProdName
SET a.Quantity = c.Quantity-b.Quantity

should work. Check the set expression, I'm not sure if I fully understand the subtraction you plan to do

RafaelCaballero
  • 1,555
  • 2
  • 17
  • 24
  • Ok, thanks! I'll try it right now. I want to subtract outprod.Quantity-inprod.Quantity then update Quantity column in Inventory. It's for updating stocks. – Aldrin Roxas Feb 13 '16 at 02:38
  • Then it should be c.Quantity-b.Quantity. I'll edit the answer – RafaelCaballero Feb 13 '16 at 02:39
  • Wait, I got confused also, hahaha. OK, so here's what I want to happen. Get sum(Quantity) Group By Product name for both inprod and outprod. After that I will subtract (inprod-outprod) where inprod=sum(Chocolate) - outprod=sum(Chocolates) then update it to Inventory. Can you show me a query for that? – Aldrin Roxas Feb 13 '16 at 03:19
  • I think it is better if you ask a new question, anyway it should be similar to: `UPDATE inventory a JOIN (select ProdName, sum(Quantity) as Quantity from inprod group by ProdName) b on a.ProdName=b.ProdName JOIN (select ProdName, sum(Quantity) as Quantity from outprod group by ProdName) c on a.ProdName=c.ProdName WHERE a.ProdName='Chocolate' SET a.Quantity = c.Quantity-b.Quantity` – RafaelCaballero Feb 13 '16 at 08:13
  • Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. – Aldrin Roxas Feb 13 '16 at 08:32
  • Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE a.ProdName='aaaaa' SET a.Quantity = c.Quantity-b.Quantity' at line 1 – Aldrin Roxas Feb 13 '16 at 08:34
  • I think this has nothing to do with the query itself...check [http://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench](http://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench) – RafaelCaballero Feb 13 '16 at 08:36
0

Answer attending to your comment below:

UPDATE inventory a 
JOIN (select ProdName, sum(Quantity) Quantity from inprod group by ProdName) b on a.ProdName=b.ProdName 
JOIN (select ProdName, sum(Quantity) Quantity from outprod group by ProdName) c on a.ProdName=c.ProdName      
SET a.Quantity = c.Quantity-b.Quantity
WHERE a.ProdName='Chocolate'
RafaelCaballero
  • 1,555
  • 2
  • 17
  • 24