CODEWITHSUNDEEP
mysqljoinsql-update
Earnest Overly
Earnest Overly

Reputation: 15

subtract value in one table from value in second table - Set result as value in second table

I have two tables. First table is Shipping with columns Product, Quantity and ID. Second table is Inventory with columns Product and Total.

I want to take the value of Shipping.Quantity and subtract it from the value in Inventory.Total WHERE the Product values match in each table and Shipping.ID is value in url ($onum). Then I want to set the new value as Inventory.Total for that product.

Best I could come up with:

UPDATE Inventory.Total 
CROSS JOIN Shipping 
SET Inventory.total=(Inventorytotal-Shipping.Quantity) 
WHERE Inventory.Product=Shipping.Product 
  AND Shipping.ID=$onum

Upvotes: 0

Views: 2706

Answers (1)

AnandPhadke
AnandPhadke

Reputation: 13506

UPDATE Inventory join Shipping 
ON Inventory.Product=Shipping.Product 
SET Inventory.Total=Inventory.Total-Shipping.Quantity
WHERE Shipping.ID=$ID

Upvotes: 1

Related Questions