SQL UPDATE with JOIN
SQL UPDATE JOIN means we will update one table using another table and join condition.
Let us take an example of a customer table. I have updated customer table that contains latest customer details from another source system. I want to update the customer table with latest data. In such case, I will perform join between target table and source table using join on customer ID.
Let's see the syntax of SQL UPDATE query with JOIN statement.
INNER JOIN
Customer_table
ON customer_table.rel_cust_name = customer_table.cust_id
SET customer_table.rel_cust_name = customer_table.cust_name
How to use multiple tables in SQL UPDATE statement with JOIN
Let's take two tables, table 1 and table 2.
Create table1
INSERT INTO table1 (col1, col2, col3)
SELECT 1, 11, 'FIRST'
UNION ALL
SELECT 11,12, 'SECOND'
UNION ALL
SELECT 21, 13, 'THIRD'
UNION ALL
SELECT 31, 14, 'FOURTH'
Create table2
INSERT INTO table2 (col1, col2, col3)
SELECT 1, 21, 'TWO-ONE'
UNION ALL
SELECT 11, 22, 'TWO-TWO'
UNION ALL
SELECT 21, 23, 'TWO-THREE'
UNION ALL
SELECT 31, 24, 'TWO-FOUR'
Now check the content in the table.
| Col 1 | Col 2 | Col 3 | |
|---|---|---|---|
| 1 | 1 | 11 | First |
| 2 | 11 | 12 | Second |
| 3 | 21 | 13 | Third |
| 4 | 31 | 14 | Fourth |
| Col 1 | Col 2 | Col 3 | |
|---|---|---|---|
| 1 | 1 | 21 | Two-One |
| 2 | 11 | 22 | Two-Two |
| 3 | 21 | 23 | Two-Three |
| 4 | 31 | 24 | Two-Four |
Our requirement is that we have table 2 which has two rows where Col 1 is 21 and 31. We want to update the value from table 2 to table 1 for the rows where Col 1 is 21 and 31.
We want to also update the values of Col 2 and Col 3 only.
The most easiest and common way is to use join clause in the update statement and use multiple tables in the update statement.
SET Col 2 = t2.Col2,
Col 3 = t2.Col3
FROM table1 t1
INNER JOIN table 2 t2 ON t1.Col1 = t2.col1
WHERE t1.Col1 IN (21,31)
Check the content of the table
SELECT FROM table 1
SELECT FROM table 2
| Col 1 | Col 2 | Col 3 | |
|---|---|---|---|
| 1 | 1 | 11 | First |
| 2 | 11 | 12 | Second |
| 3 | 21 | 23 | Two-Three |
| 4 | 31 | 24 | Two-Four |
| Col 1 | Col 2 | Col 3 | |
|---|---|---|---|
| 1 | 1 | 21 | First |
| 2 | 11 | 22 | Second |
| 3 | 21 | 23 | Two-Three |
| 4 | 31 | 24 | Two-Four |
Here we can see that using join clause in update statement. We have merged two tables by the use of join clause.
