This article explores the SQL Server Update Join statement in T-SQL for SQL Server.
Introduction
We use the Update statement in SQL Server for updating an existing row in a table. We can update all records or few records based on criteria specified in a where clause. Usually, we update a single table with the SQL Update statement.
In a relational database, it is best practice to use normalization in database design. In database normalization, we use multiple tables and define the relationship between them. We can retrieve records from multiple tables with SQL Joins.
Now a question arises: Can we update multiple tables using SQL Server Update Join? Let’s explore in this article.
Let’s create a customer’s table and insert few records in it.
Prepare environment for demonstration
Create a Customers table and insert few records in it:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE [dbo].[Customers] ([id] [INT] identity(1,1), [CustomerName] [VARCHAR](30) NULL, [OrderCount] [INT] NULL ) ON [PRIMARY]; GO Insert into Customers ([CustomerName],[OrderCount]) values('Raj',NULL),('Kusum',NULL),('Akshita',NULL),('John',NULL),('Dan',NULL) |
Create an Orders table and insert few records in it:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE [dbo].[Orders] ([Order_ID] [INT] IDENTITY(1, 1) NOT NULL, [CustomerID] [INT] NOT NULL, [OrderQuantity] [INT] NOT NULL, [OrderAmount] [INT] NOT NULL, CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED([Order_ID] ASC) ) ON [PRIMARY]; GO |
This table should have a foreign key constraint on [CustomerID] column of Customers table:
|
1 2 3 4 5 6 7 8 9 |
ALTER TABLE [dbo].[Orders] WITH CHECK ADD CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([CustomerID]) REFERENCES [dbo].[Customers] ([Customerid]) GO ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customers] GO Insert into [dbo].[Orders] (CustomerID, OrderQuantity,OrderAmount) values(1,100,5000) Insert into [dbo].[Orders] (CustomerID, OrderQuantity,OrderAmount) values(2,150,6879) Insert into [dbo].[Orders] (CustomerID, OrderQuantity,OrderAmount) values(3,189,7895) |
SQL Update statement
We have the environment ready for demonstration. Before we dig into multiple table updates using SQL Server Update Join, let’s look at the primary form of SQL Update statement.
Suppose we want to update [OrderQuantity] and [OrderAmount] column of orders table, we first use a SELECT statement to view the record:
|
1 2 3 |
SELECT * FROM dbo.orders WHERE CustomerID = 2; |
We can use the following SQL Update statement for updating the Orders table. Here we use SQL Alias for the Orders table. Here we replaced the Select statement with the Update statement without much change in the query:
|
1 2 3 |
Update O set [OrderQuantity]=200,[OrderAmount]=7896 FROM dbo.orders O WHERE CustomerID = 2; |
We can execute the Select statement and verify the changes:
SQL UPDATE statement with SQL JOIN
Now, let’s use SQL Join for retrieving the record from both of the tables. We use the [CustomerID] column for the Join between both of the tables:
|
1 2 3 4 5 6 |
SELECT C.CustomerName, C.OrderCount, O.OrderAmount, O.OrderQuantity FROM Customers C JOIN Orders O ON C.Customerid = O.CustomerID; |
In the output, we can see only three records. An Inner Join retrieves records that exist in both of the tables. CustomerID 1, 2, 3 exists in both the tables, and it is available in result of the SQL Join:
We can see NULL values in the [OrderCount] column of the Customers table. Suppose we want to update this column with the [OrderQuantity] of the orders table. We can use the following syntax for the SQL Server Update Join statement:
|
1 2 3 4 5 6 7 8 9 10 |
UPDATE Tablealias SET A.c1 = B.C1 ... FROM tableA A [INNER | LEFT] JOIN tableA B ON join_predicate WHERE where_predicate; |
- Specify a base table in which we want to update records. We can also use SQL Join alias instead of a table name
- Specify the column and value of the column that we want to update. We use the Set statement for specifying the values
- Use SQL Join operator and specify the table name with join conditions. We can either use an Inner Join or Left Join in this predicate
- Add Where clause to update only specific rows. It is an optional argument
The following query updates the customer table (Update C statement) with the Set operator (SET C.OrderCount = O.OrderQuantity) using the Join between Customers and Orders table (Customers C JOIN Orders O ON C.Customerid = O.CustomerID):
|
1 2 3 4 5 |
UPDATE C SET C.OrderCount = O.OrderQuantity FROM Customers C JOIN Orders O ON C.Customerid = O.CustomerID; |
It returns the output that three rows are affected:
Execute the Select join statement and verify the records. We can see it shows similar values in the [OrderCount] and [OrderQuantity] columns:
Suppose on a new financial year, we archive old customer records and start with the zero in the [OrderQuantity] columns of the orders table. We can execute the following SQL Server Update Join statement, and it updates the [OrderQuantity] columns of orders table:
|
1 2 3 4 5 |
UPDATE O SET O.OrderQuantity = 0 FROM Customers C JOIN Orders O ON C.Customerid = O.CustomerID; |
We cannot update multiple tables together using SQL Server Update Join. If we try updating multiple columns belonging to different tables, we get the following error message:
The multi-part identifier “O.OrderAmount” could not be bound.
|
1 2 3 4 5 6 7 |
UPDATE C SET C.OrderCount = 0, O.OrderAmount = 0, O.OrderQuantity = 0 FROM Customers C JOIN Orders O ON C.Customerid = O.CustomerID; |
We can use multiple update statements in this case. For example, the following query updates the [OrderCount] column of the customers table:
|
1 2 3 4 5 |
UPDATE C SET C.OrderCount = 0 FROM Customers C JOIN Orders O ON C.Customerid = O.CustomerID; |
Next Update statements, updates the [OrderAmount] and [OrderQuantity] columns value as zero in the Orders table:
|
1 2 3 4 5 6 |
UPDATE O SET O.OrderAmount = 0, O.OrderQuantity = 0 FROM Customers C JOIN Orders O ON C.Customerid = O.CustomerID; |
SQL Server Update Join with Left Join
In previous examples, we use inner join for retrieving records in which similar customer id exists in both Customers and Orders table. We can use left join to get matched row along with an unmatched row from the left-hand side table.
The following Select statement shows the output of a Select statement with Left Join:
|
1 2 3 4 5 6 |
SELECT C.CustomerName, C.OrderCount, O.OrderAmount, O.OrderQuantity FROM Customers c left JOIN orders O ON O.Customerid = C.CustomerID; |
Here, in the output, we can see Customer John and Dan. The values for [OrderCount], [OrderAmount] and [OrderQuantity] columns are NULL for these customers:
We can use the Update statement with Left Join as well, and it updates the records with NULL values. As highlighted earlier, we cannot use a single Update statement for updating multiple columns from different tables.
The following update statement updates the [OrderCount] value as zero for customers having [OrderCount] column value NULL:
|
1 2 3 4 5 6 |
UPDATE C SET C.OrderCount = 0 FROM Customers C right JOIN Orders O ON C.Customerid = O.CustomerID where Ordercount IS NULL |
In the following image, we see the actual execution plan of the above SQL Update statement. It uses the clustered index update operator for updating records in the customer’s table:
The following update statement updates the [OrderAmount] and [OrderQuantity] value as zero for customers having [OrderAmount] column value NULL:
|
1 2 3 4 5 6 7 |
UPDATE O SET O.OrderAmount = 0, O.OrderQuantity = 0 FROM Customers C left JOIN Orders O ON C.Customerid = O.CustomerID where OrderAmount IS NULL |
Similarly, we can use the following query with Right Join in the SQL Server Update Join statement:
|
1 2 3 4 5 6 |
UPDATE C SET C.OrderCount = 0 FROM Customers C Right JOIN Orders O ON C.Customerid = O.CustomerID where Ordercount IS NULL |
Conclusion
In this article, we explored SQL Server Update Join for updating tables specified with a Join clause. We should be careful in doing updates in a relational table of production instance as it might cause issues due to an incorrect value, column name, etc.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023