Rajendra Gupta
Error message

An overview of the SQL Server Update Join

December 5, 2019 by

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:

Customers table data

Create an Orders table and insert few records in it:

This table should have a foreign key constraint on [CustomerID] column of Customers table:

Orders table data

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:

SQL Update statement

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:

We can execute the Select statement and verify the changes:

Verify the updates

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:

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:

SQL Update statement with 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:

  • 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):

It returns the output that three rows are affected:

Output after update

Execute the Select join statement and verify the records. We can see it shows similar values in the [OrderCount] and [OrderQuantity] columns:

Verify update

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:

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.

Error message

We can use multiple update statements in this case. For example, the following query updates the [OrderCount] column of the customers table:

Next Update statements, updates the [OrderAmount] and [OrderQuantity] columns value as zero in the Orders table:

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:

Here, in the output, we can see Customer John and Dan. The values for [OrderCount], [OrderAmount] and [OrderQuantity] columns are NULL for these customers:

SQL Update with left join

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:

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:

actual execution plan

The following update statement updates the [OrderAmount] and [OrderQuantity] value as zero for customers having [OrderAmount] column value NULL:

Similarly, we can use the following query with Right Join in the SQL Server Update Join statement:

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.

Rajendra Gupta
10,942 Views