What is SQL UPDATE with JOIN?

sql-update-with-join-example

SQL UPDATE with JOIN is used to update one table using another table. The main function of the SQL UPDATE with JOIN is to perform cross-table updates.

In the above diagram, we are using Table 2's col2 and inserting it into table 1. On the right-hand side, we have the resultant table 1. In the resultant table, we have the whole table1 as it is just the col2 of table2. We can achieve this using UPDATE with JOIN in SQL.

We often use the join clauses, either INNER JOIN or LEFT JOIN. We can use these join clauses in SQL servers, in the UPDATE statement to perform cross-table updates.

Let's see these join clauses, and how to use them with UPDATE in action.

Syntax

Specify the name of the table (table_to_update) that you want to update in the UPDATE clause.
Next, specify the new value for each column of the updated table in the SET clause.
In FROM clause we have to specify the table which we want to update.
After that, use either INNER JOIN or LEFT JOIN to join to another table (other_table) using a join predicate specified after the ON keyword.
At the end, we can add an optional WHERE clause to specify rows to be updated based on the condition mentioned.

Working of SQL UPDATE with JOIN

Performing simple update operations using the UPDATE statement in SQL is easy. Even in tables, we can update all of the columns in the table, or just some of them, by stating the condition in the WHERE clause.

The above task can be done easily using just the UPDATE statement. However, to update/assign the column values of one table using another table's column values we have to use UPDATE with JOIN in SQL.

Note: At a time, only columns of a single table can be updated in an update join statement.

The update join statement work with inner join that takes into account the commonly matched records of both the tables and also with a left join that considers all the records of the left side table and the matched records while updating.

The UPDATE with JOIN in SQL gives us one more optional functionality to us. We can update all or some of the records by specifying the condition in the WHERE clause.

Examples of SQL Updates with JOIN

Let's first create two tables and then perform the UPDATE with the JOIN operation on them.

Create table1:

Output(table1):

col1	col2	col3
11	21	FIRST
12	22	SECOND
13	23	THIRD
14	24	FOURTH

Create table2:

Output(table2):

col1	col2	col3
31	21	Two_One
32	22	Two_Two
33	23	Two_Three
34	24	Two_Four

Now, our task is to update the value from table 2 to table 1 for the rows where Col 2 is 23 and 24. We also want to update the col1 and col3 values.

To do this, we'll use the UPDATE with the JOIN query.

Now, if we check the output of our table1 then it will be different from our previous output of table1.

Output(table1):

col1	col2	col3
11	21	FIRST
12	22	SECOND
33	23	Two_Three
34	24	Two_Four

Learn More

Learn more about SQL UPDATE and other statements:

Conclusion

We can use the UPDATE statement to update records of a single or multiple table(s). However, in this article, we learned how we can update the records of one table using the column values of another table using the UPDATE with a JOIN statement in SQL.