Wednesday, June 3, 2009

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

Please visit my new Web Site

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.


Want to say something?

Want To Search More?
Google Search on Internet
Subscribe RSS Subscribe RSS
Article Categories
  • Gridview
  • Javascript
  • AJAX
  • Sql server
  • XML
  • CSS
  • Free Web Site Templates
  • Free Desktop Wallpapers
  • TopOfBlogs
    Free ASP.NET articles,C#.NET,VB.NET tutorials and Examples,Ajax,SQL Server,Javascript,Jquery,XML,GridView Articles and code examples -- by Shawpnendu Bikash