In MySQL, you can use the JOIN clauses in the UPDATE statement to perform the cross-table update.
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
UPDATE customer_table 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 TABLE table1 (column1 INT, column2 INT, column3 VARCHAR (100)) 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 TABLE table2 (column1 INT, column2 INT, column3 VARCHAR (100)) 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, Let's check the contents of the table we have just created.
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.
UPDATE table 1 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)
Now, Let's check the contents of the table we have just
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.