MySQL Update Join for Cross-Table Update

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:

UPDATE db.table1, db.table2,

[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.

CREATE DATABASE IF NOT EXISTS sample_db;

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:

USE sample_db;

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:

SELECT * FROM sample_db.tb2;

The output as shown below:

mysql> SELECT * FROM sample_db.tb2;

+------+--------+-------+------+

| 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:

https://dev.mysql.com/doc/refman/8.0/en/update.html

https://dev.mysql.com/doc/refman/8.0/en/join.html

from Linux Hint https://ift.tt/3432oO8