MySQL UPDATE JOIN: Update Rows from Another Table

MySQL’s multiple-table UPDATE syntax can join a target table to other tables and use their values in the SET expressions. This is often called UPDATE JOIN. The joined rows determine which target rows are updated; the WHERE clause can further restrict them.

Syntax

UPDATE table_references
SET assignment_list
[WHERE where_condition];

table_references can include INNER JOIN, LEFT JOIN, and their ON or USING conditions. For details about join expressions, see the MySQL JOIN guide.

Example: update employee salaries from a lookup table

Create temporary tables and sample data in the same session:

CREATE TEMPORARY TABLE merits (
    performance INT PRIMARY KEY,
    raise_pct DECIMAL(4,3) NOT NULL
);

CREATE TEMPORARY TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(80) NOT NULL,
    performance INT NULL,
    salary DECIMAL(10,2) NOT NULL
);

INSERT INTO merits (performance, raise_pct)
VALUES (1, 0.000), (2, 0.010), (3, 0.030), (4, 0.050), (5, 0.080);

INSERT INTO employees (employee_id, employee_name, performance, salary)
VALUES
    (1, 'Maya', 3, 65000.00),
    (2, 'Noah', 5, 125000.00),
    (3, 'Lina', NULL, 43000.00);

Use INNER JOIN to update employees whose performance has a matching percentage in merits:

UPDATE employees AS e
INNER JOIN merits AS m
    ON e.performance = m.performance
SET e.salary = e.salary * (1 + m.raise_pct);

Maya and Noah are updated. Lina has no matching performance value, so the INNER JOIN excludes her row.

Update rows without a match

Use LEFT JOIN when you need to target employees with no matching row in the lookup table. For example, this query gives those employees a 1.5% increase:

UPDATE employees AS e
LEFT JOIN merits AS m
    ON e.performance = m.performance
SET e.salary = e.salary * 1.015
WHERE m.performance IS NULL;

The merits.performance column is a primary key, so it cannot be NULL. Testing it for NULL identifies employees with no match.

Important behavior

• In a multiple-table UPDATE, MySQL updates each matching target row once, even if it matches the join more than once. Keep the source key unique when the new value must be unambiguous.
• Multiple-table UPDATE does not support ORDER BY or LIMIT.
• For a single-table update without a join, see the MySQL UPDATE tutorial.

For the full syntax and behavior, see MySQL’s official UPDATE documentation.