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.
The general syntax for a basic MySQL update join query is:
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.
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:
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)
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: