In the latest versions of MySQL, you can perform a cross-table update, also known as a correlation table update where you can join two or more tables. Using MySQL JOIN (INNER and LEFT) and UPDATE query, we can perform a cross-table update in very simple steps.
This tutorial will walk you through in how to perform MySQL cross-table updates using native MySQL commands.
Basic Syntax
The general syntax for a basic MySQL update join query is:
[LEFT JOIN | INNER JOIN] db.table1 ON db.table1.column1 = db.table2.column2
SET db.table1.colum2 = db.table2.column2, db.table2.column3 = Expression
WHERE [condition]
Let us break down the query above into smaller parts so we can understand it better.
We begin by defining the main table. In this case, (db.table1) followed by the table we want to join using the update statement, in this case, db.table2.
NOTE: It is important to specify at least one table after the update statement; otherwise, the rows in the table will not update after the query.
In the second part of the query, we specify the specific kind of JOIN we want to perform, i.e., INNER or LEFT JOIN and a join predicate. Always set the JOIN query immediately after the UPDATE query.
A join predicate is a specific JOIN condition that evaluates to a Boolean value.
In the following part, we set new values to the columns of db.table1 and db.table2.
Lastly, we set a condition using the WHERE clause, which helps to limit the rows that undergo the update.
Example Use case
Suppose you have two tables, called tb1 and tb2; consider a query below to create tables and add data.
USE sample_db;
CREATE TABLE tb1 (
col1 INT(11) NOT NULL,
col2 INT NOT NULL,
PRIMARY KEY (col1)
);
CREATE TABLE tb2 (
col1 INT NOT NULL AUTO_INCREMENT,
col2 VARCHAR(100) NOT NULL,
col3 INT DEFAULT NULL,
col4 INT DEFAULT NULL,
PRIMARY KEY (col1)
);
INSERT INTO tb1(col1, col2) VALUES(1,0), (2,1),(3,3),(4,4),(5,5);
INSERT INTO tb2(col2,col3,col4) VALUES ("First", 1, 500),("Second", 2, 1000),("Third", 3, 1500),("Fourth", 4, 2000),("Fifth", 5, 2500);
In the tables created using the query above, we can perform an update query using INNER JOIN statement as tables are linked on col2.
Consider the query below:
UPDATE tb2 INNER JOIN tb1 ON tb2.col3 = tb1.col1 SET col3 = col4 + col4 * 5;
Since we want to update the data in the second table (col3) where we get the values of the columns and multiply by 5, in the example above, we take out the WHERE condition because we want to update all the records in the specified table.
You can confirm this using the select query as shown below:
The output as shown below:
+------+--------+-------+------+
| col1 | col2 | col3 | col4 |
+------+--------+-------+------+
| 1 | First | 3000 | 500 |
| 2 | Second | 6000 | 1000 |
| 3 | Third | 9000 | 1500 |
| 4 | Fourth | 12000 | 2000 |
| 5 | Fifth | 15000 | 2500 |
+------+--------+-------+------+
5 rows in set (0.00 sec)
Conclusion
In this quick tutorial, we discussed how to use the MySQL update join query which allows you to combine tables and update values accordingly.
To learn more about how you can expand this feature, consider the resources provided below:
