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


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

  1. prodstock table

    enter image description here

  2. OrderDetails Table

    enter image description here

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

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

Leave a Reply

(*) Required, Your email will not be published