What is SQL UPDATE with JOIN?
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.
- 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.
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.
Learn more about SQL UPDATE and other statements:
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.