How to write TSQL for inner/left/right/full join in update query

Please visit my new Web Site WWW.Codedisplay.com

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.

Let 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 prees the button to update. So what happen? Now we need to modify the ProductStock table based on shipeed quantity from Shipment table.

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.