Summary: in this tutorial, you will learn how to use MySQL UPDATE JOIN statement to perform cross-table update. We will show you step by step how to use
INNER JOIN clause and
LEFT JOIN clause with the
MySQL UPDATE JOIN syntax
You often use
JOIN clauses to query records in a table that have (in case of INNER JOIN) or do not have (in case of LEFT JOIN) corresponding records in another table. In MySQL, you can use the
JOIN clauses in the UPDATE statement to perform cross-table update.
The syntax of the MySQL
UPDATE JOIN is as follows:
UPDATE T1, T2, [INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1 SET T1.C2 = T2.C2, T2.C3 = expr WHERE condition
Let’s examine the MySQL
UPDATE JOIN syntax in greater detail:
- First, you specify the main table (
T1) and the table that you want the main table to join to (
T2) after the
UPDATEclause. Notice that you must specify at least one table after the
UPDATEclause. The data in the table that is not specified after the
UPDATEclause is not updated.
- Second, you specify a kind of join you want to use i.e., either
LEFT JOINand a join condition. Notice that the
JOINclause must appear right after the
- Third, you assign new values to the columns in T1 and/or T2 tables that you want to update.
- Fourth, the condition in the
WHEREclause allows you to limit the rows to update.
If you follow the UPDATE statement tutorial, you notice that there is another way to update data cross-table using the following syntax:
UPDATE T1, T2 SET T1.c2 = T2.c2, T2.c3 = expr WHERE T1.c1 = T2.c1 AND condition
UPDATE statement works the same as
UPDATE JOIN with implicit
INNER JOIN clause. It means you can rewrite the above statement as follows:
UPDATE T1,T2 INNER JOIN T2 ON T1.C1 = T2.C1 SET T1.C2 = T2.C2, T2.C3 = expr WHERE condition
Let’s take a look at some examples of using the
UPDATE JOIN statement to having a better understanding.
MySQL UPDATE JOIN examples
We are going to use a new sample database in these examples. The sample database contains 2 tables:
employeestable stores employee data with employee id, name, performance and salary.
meritstable stores performance and merit’s percentage.
The SQL script for creating and loading data in this sample database is as follows:
CREATE DATABASE IF NOT EXISTS empdb; -- create tables CREATE TABLE merits ( performance INT(11) NOT NULL, percentage FLOAT NOT NULL, PRIMARY KEY (performance) ); CREATE TABLE employees ( emp_id INT(11) NOT NULL AUTO_INCREMENT, emp_name VARCHAR(255) NOT NULL, performance INT(11) DEFAULT NULL, salary FLOAT DEFAULT NULL, PRIMARY KEY (emp_id), CONSTRAINT fk_performance FOREIGN KEY(performance) REFERENCES merits(performance) ); -- insert data for merits table INSERT INTO merits(performance,percentage) VALUES(1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08); -- insert data for employees table INSERT INTO employees(emp_name,performance,salary) VALUES('Mary Doe', 1, 50000), ('Cindy Smith', 3, 65000), ('Sue Greenspan', 4, 75000), ('Grace Dell', 5, 125000), ('Nancy Johnson', 3, 85000), ('John Doe', 2, 45000), ('Lily Bush', 3, 55000);
MySQL UPDATE JOIN example with INNER JOIN clause
Suppose you want to adjust the salary of employees based on their performance. The merit’s percentages are stored in the
merits table therefore you have to use
UPDATE INNER JOIN statement to adjust the salary of employees in the
employees table based on the
percentage stored in the
merits table. The link between the
merit tables is
performance field. See the following query:
UPDATE employees INNER JOIN merits ON employees.performance = merits.performance SET salary = salary + salary * percentage
How the query works.
- We specify only the
UPDATEclause because we want to update data in the
- For each employee record in the
employeestable, the query checks the its performance value against the performance value in the
meritstable. If it finds a match, it gets the
meritstable and update the
salarycolumn in the
- Because we omit the
WHEREclause in the
UPDATEstatement, all the records in the
employeestable get updated.
MySQL UPDATE JOIN example with LEFT JOIN
Suppose the company hires two more employees:
INSERT INTO employees(emp_name,performance,salary) VALUES('Jack William',NULL,43000), ('Ricky Bond',NULL,52000);
Because these employees are new hires so their performance data is not available or
To increase the salary for new hires, you cannot use the
UPDATE INNER JOIN statement because their performance data is not available in the
merit table. This is why the
UPDATE LEFT JOIN comes to the rescue.
UPDATE LEFT JOIN statement basically updates a record in a table when it does not have a corresponding record in another table. For example, you can increase the salary for a new hire by 1.5% using the following statement:
UPDATE employees LEFT JOIN merits ON employees.performance = merits.performance SET salary = salary + salary * 0.015; WHERE merits.percentage IS NULL
In this tutorial, we have shown you how to use MySQL
UPDATE JOIN with
INNER JOIN and
LEFT JOIN to perform cross-table update.