How to write an update query using joins and the arithmetic operator

Issue

I Have two tables. The first is prodstock, and the second is orderDetails: follows:

1. prodstock table

   

2. OrderDetails Table

   

When I change the status of the order table and deliver, the quantity in my product table should decrease by the quantity in the order table, but I have no idea how to construct a query that does that.

Solution

I made this query which works for me:

UPDATE prodstock 
INNER JOIN orderdetails ON prodstock.cat_id = orderdetails.cat_id AND
           prodstock.dyenumber = orderdetails.dyenumber 
SET prodstock.total_stock = prodstock.total_stock - orderdetails.qty
WHERE orderdetails.order_id = 2;

Answered By – Sahil Mangukiya

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0