I Have two tables. The first is prodstock, and the second is orderDetails: follows:
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.
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