Most of the beginner generally stuck with a problem like how to make a query to update a table by another table or from multiple table data with some conditions. In this post i will try to focus on this area how we can resolve this issue.
A scenario like, you have an online e-commerce site where you sale some products. So when a customer place an order you need to forward this order for shipment as well as you want to update the product stock at a time. Table structure & sample data given below:
Now one of your employee from shipment section enter the shipped quantity from your application and press the button to update. So what happen? Now we need to modify the Product Stock table based on shipped quantity from Shipment table.
So Our Query Should Be:
UPDATE ProductStock SET PhysicalQty=PhysicalQty-Shipment.ShippedQty FROM ProductStock INNER JOIN Shipment ON ProductStock.ProductID=Shipment.ProductID
After running this query the output should be:
So what we learn we can easily write a single query to update multiple rows based on another or more than one table data by using SQL Server JOIN operation.