SQL SERVER UPDATE JOIN

The main purpose of this article is to show you how to use the UPDATE JOIN command to edit existing records in a table using a JOIN between the two tables. We can use the UPDATE statement and the JOIN statement separately or together.

The UPDATE query is a DML statement for modifying the existing data in a table. We must use the SET and WHERE clauses in the UPDATE query. The SET clause is used to alter the values of the column specified in the WHERE clause. The JOIN clause is used to get data by joining many tables in a single query.

What is UPDATE JOIN?

The UPDATE JOIN is one of the special cases of the UPDATE query in which rows in the target table are updated with values from the source table for the same column or field. In other words, this statement performs cross-table updates, which means we can update one table using another table with the JOIN clause condition. It can update and change data when multiple tables are joined with the help of PRIMARY and FOREIGN keys and a join condition. We can update single or multiple columns at a time using the UPDATE query.

Syntax

The syntax of UPDATE JOIN statement is given below:

The above syntax parameters are explained below:

First, we have to specify the table names (Tab1 and Tab2) for modification. We must specify at least one table after the UPDATE clause. After that, we have to assign the new values for every column of the altered table. Then, in the FROM clause, we specify the source table from which we want to make updation. Next, we need to write the join types, either INNER or LEFT JOIN, and then a join condition after the ON keyword. Finally, the optional WHERE clause is used to limit rows for updation.

UPDATE JOIN Example

Let us first create the following tables to perform the individual statements before going into how the joins work with the UPDATE statement. The below sample code will create two tables named Merits and Employee, and both tables are related through a foreign key. Here, the "Merits" is a parent table, and the "Employee" is the child table.

Code script for 'Merit' table:

Code script for 'Employee' table:

We can verify the table by using the SELECT statement as below:

Executing the above statement will return the following output:

UPDATE with INNER JOIN

If we want to update the employee's salary based on their performance, we need to use the UPDATE INNER JOIN statement. This statement will update an employee's salary in the Employee table based on the performance percentage (%) contained in the Merits table. We must use the common field performance to join the Employees and Merits tables. Consider the following query to complete this task:

Executing the statement will display the below message:

If we query the Employee table again, we will see that the values in the salary column are updated successfully:

Let's look at how SQL Server handles this query. In the query, we have just mentioned the Employee table after the UPDATE clause because we only want to edit the record in the Employee table, not both.

This query compares the values of the "Employee" table's performance column to the "Merits" table's performance column. If the matched performance field is found, the Merits table's percentage is used to update the Employees table's salary column. This query will update each record of the Employees table because we did not specify the WHERE clause in the UPDATE JOIN statement.

UPDATE with LEFT JOIN

In the previous example, we used an inner join to update the employee's salary in which a similar performance column exists in both Merits and Employee tables. Now, we will see how to use UPDATE statement with LEFT JOIN. To understand this concept better, we first need to add two additional rows to the Employees table:

These employee's performance records are unavailable because they are new hires. Take a look at the following output:

Suppose we want to update the salary of newly hired employees. In such a case, the UPDATE INNER JOIN query cannot be used. It's because their performance data in the Merits table isn't available. As a result, we'll use the UPDATE LEFT JOIN statement to meet this need. The UPDATE LEFT JOIN statement updates a row in a table when no matching records are found in the other table's corresponding row.

For example, if we want to increment a recently hired employee's compensation by 4.5 percent, we can accomplish this task by using the following statement:

Executing the statement will return the following result, which shows that the salary of newly hired employees has been successfully updated.

This article will help us learn about the UPDATE JOIN statement, allowing us to replace existing data in one table with new data from another table using the JOIN clause condition. This query is useful when we need to change certain columns in the WHERE clause along with the INNER JOIN or LEFT JOIN clauses. We should be careful when updating a relational table in a production instance because an erroneous value, column name, or other error could cause issues.