SQL to Update Rows in one Table from another Table

Some times we need to Update one table records based on another table value. One classical example is: Updating Product table quantity after placing an Order. Some developer Update Product Quantity by iterating through each order line item. But we can do this in a single query. Here in this article I will show you “How to Update Rows in one table from another table in SQL SERVER using a Single SQL Query“.

To do the example create 2 Tables:

CREATE TABLE [dbo].[Product](
	[ID] [int] NULL,
	[ProductName] [varchar](max) NULL,
	[Quantity] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[OrderLineItem](
	[ID] [int] NULL,
	[OrderID] [int] NULL,
	[ProductID] [int] NULL,
	[Quantity] [int] NULL
) ON [PRIMARY]

Now Insert 2 Records to Test:

INSERT INTO Product
VALUES(1,'TestProduct',100)

INSERT INTO OrderLineItem
VALUES(1,1,1,10)

Now SQL to Update Records in one Table From another Table:

UPDATE dbo.Product
SET Product.Quantity=Product.Quantity-OrderLineItem.Quantity
FROM OrderLineItem
WHERE Product.ID=OrderLineItem.ProductID AND ORDERID=1

Output/Outcome:
SQL Server SQL to Update values in one table from another table

Posted in Database, SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

     

*