MySQL UPDATE JOIN | Cross-Table Update in MySQL

Summary: in this tutorial, you will learn how to use the MySQL UPDATE JOIN statement to perform the cross-table update.

Introduction to MySQL UPDATE JOIN statement

You often use joins to query rows from a table that have (in the case of INNER JOIN) or may not have (in the case of LEFT JOIN) matching rows in another table.

In MySQL, you can also use the JOIN clauses in the UPDATE statement to update rows in one table based on values from another table. The UPDATE JOIN statement is useful when you need to modify data across related tables.

The syntax of the MySQL UPDATE JOIN is as follows:

UPDATE T1
[INNER JOIN | LEFT JOIN] T2 ON T1.C1 = T2.C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition;Code language: SQL (Structured Query Language) (sql)

How it works:

First, specify the table that you want to update after the UPDATE keyword (T1).
Second, use either INNER JOIN or LEFT JOIN and a join predicate. The JOIN clause must appear right after the UPDATE clause.
Third, assign new values to the columns of the T1 table that you want to update data.
Finally, specify a condition in the WHERE clause to filter the rows for updating.

If you follow the UPDATE statement tutorial, you will notice that there is another way to update the data across tables using the following syntax:

UPDATE T1
SET T1.c2 = T2.c2,
    T2.c3 = expr
WHERE T1.c1 = T2.c1 AND condition;Code language: SQL (Structured Query Language) (sql)

This UPDATE statement works the same as UPDATE JOIN with an implicit INNER JOIN clause. It means you can rewrite the above statement as follows:

UPDATE T1
INNER JOIN T2 ON T1.C1 = T2.C1
SET T1.C2 = T2.C2,
      T2.C3 = expr
WHERE condition;Code language: SQL (Structured Query Language) (sql)

MySQL UPDATE JOIN examples

Let’s explore some examples of using the UPDATE JOIN statement to have a better understanding.

We’ll create a new database called hr that consists of two tables:

The employees table stores employee data with employee id, name, performance, and salary.
The merits table stores employee performance and merit percentage.

The following statements create and load data in the hr sample database:

CREATE DATABASE IF NOT EXISTS hr;

USE hr;

CREATE TABLE merits (
  performance INT PRIMARY KEY, 
  percentage DEC(11, 2) NOT NULL
);

CREATE TABLE employees (
  emp_id INT AUTO_INCREMENT PRIMARY KEY, 
  emp_name VARCHAR(255) NOT NULL, 
  performance INT DEFAULT NULL, 
  salary DEC(11, 2) DEFAULT NULL, 
  FOREIGN KEY (performance) REFERENCES merits (performance)
);

INSERT INTO merits(performance, percentage) 
VALUES 
  (1, 0), 
  (2, 0.01), 
  (3, 0.03), 
  (4, 0.05), 
  (5, 0.08);

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);Code language: SQL (Structured Query Language) (sql)

MySQL UPDATE JOIN example with INNER JOIN clause

Suppose you want to increment each employee’s salary by a percentage based on their performance.

The following statement updates the salary column in the employees table by performing an INNER JOIN with the merits table based on matching values in the performance column:

UPDATE 
  employees 
  INNER JOIN merits ON employees.performance = merits.performance 
SET 
  salary = salary + salary * percentage;Code language: SQL (Structured Query Language) (sql)

Output:

Query OK, 6 rows affected (0.01 sec)
Rows matched: 7  Changed: 6  Warnings: 0Code language: CSS (css)

How the query works:

UPDATE employees: Specifies that we want to update the employees table.
INNER JOIN merits ON employees.performance = merits.performance: Performs an INNER JOIN with the merits table based on the condition that the performance column values match between the employees and merits tables. This means only the rows with matching performance values in both tables will be considered for the update.
SET salary = salary + salary * percentage: Updates the salary column in the employees table. Each employee’s salary is updated based on the percentage: salary + salary * percentage.

Because the UPDATE statement does not have the WHERE clause, it updates all rows employees table.

The following statement retrieves data from the employees table to verify the updates:

SELECT * FROM employees;

Output:

+--------+---------------+-------------+--------+
| emp_id | emp_name      | performance | salary |
+--------+---------------+-------------+--------+
|      1 | Mary Doe      |           1 |  50000 |
|      2 | Cindy Smith   |           3 |  66950 |
|      3 | Sue Greenspan |           4 |  78750 |
|      4 | Grace Dell    |           5 | 135000 |
|      5 | Nancy Johnson |           3 |  87550 |
|      6 | John Doe      |           2 |  45450 |
|      7 | Lily Bush     |           3 |  56650 |
+--------+---------------+-------------+--------+
7 rows in set (0.00 sec)Code language: JavaScript (javascript)

MySQL UPDATE JOIN example with LEFT JOIN

Suppose the company has two new hires employees with the performances are NULL:

TRUNCATE TABLE employees;

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),
  ('Jack William', NULL, 43000), 
  ('Ricky Bond', NULL, 52000);Code language: SQL (Structured Query Language) (sql)

Because these employees are new hires so their performance data is not available or NULL:

SELECT * FROM employees;

Output:

+--------+---------------+-------------+--------+
| emp_id | emp_name      | performance | salary |
+--------+---------------+-------------+--------+
|      1 | Mary Doe      |           1 |  50000 |
|      2 | Cindy Smith   |           3 |  66950 |
|      3 | Sue Greenspan |           4 |  78750 |
|      4 | Grace Dell    |           5 | 135000 |
|      5 | Nancy Johnson |           3 |  87550 |
|      6 | John Doe      |           2 |  45450 |
|      7 | Lily Bush     |           3 |  56650 |
|      8 | Jack William  |        NULL |  43000 |
|      9 | Ricky Bond    |        NULL |  52000 |
+--------+---------------+-------------+--------+
9 rows in set (0.00 sec)Code language: PHP (php)

To raise the salary for all employees including new hires, you cannot use the UPDATE INNER JOIN statement because the performance scores of the new hires are not available in the merits table. This is where the UPDATE LEFT JOIN statement comes to the rescue.

The UPDATE LEFT JOIN statement updates a row in a table when it does not have a corresponding row in another table.

For example, you can increase the salary for a new hire by 1.5% and other employees based on their performances using the following statement:

UPDATE 
  employees 
  LEFT JOIN merits ON employees.performance = merits.performance 
SET 
  salary = salary + salary * COALESCE(percentage, 0.015);Code language: SQL (Structured Query Language) (sql)

Output:

Query OK, 8 rows affected (0.01 sec)
Rows matched: 9  Changed: 8  Warnings: 0Code language: CSS (css)

The salary of the employee (Mary Doe) who has a performance score of 1 was not updated. Therefore, we have 8 rows changed.

The following statement retrieves the data from the employees table to verify the updates:

SELECT * FROM employees;

Output:

+--------+---------------+-------------+--------+
| emp_id | emp_name      | performance | salary |
+--------+---------------+-------------+--------+
|      1 | Mary Doe      |           1 |  50000 |
|      2 | Cindy Smith   |           3 |  66950 |
|      3 | Sue Greenspan |           4 |  78750 |
|      4 | Grace Dell    |           5 | 135000 |
|      5 | Nancy Johnson |           3 |  87550 |
|      6 | John Doe      |           2 |  45450 |
|      7 | Lily Bush     |           3 |  56650 |
|      8 | Jack William  |        NULL |  43645 |
|      9 | Ricky Bond    |        NULL |  52780 |
+--------+---------------+-------------+--------+
9 rows in set (0.00 sec)Code language: PHP (php)

Summary

Use the MySQL UPDATE JOIN with the INNER JOIN or LEFT JOIN clauses to perform cross-table updates.

Was this tutorial helpful?