UPDATE with JOIN in MySQL

Introduction

You use UPDATE with JOIN to change data in one table based on matching data in another table. It helps update related information easily.

When you want to update a customer's address using data from a separate address table.

When you need to fix product prices based on new rates stored in another table.

When you want to update employee department names using a department info table.

When you want to correct order statuses by joining with a status reference table.

Syntax

MySQL

UPDATE table1
JOIN table2 ON table1.common_field = table2.common_field
SET table1.field_to_update = new_value
WHERE some_condition;

The JOIN clause connects the tables using a common field.

The WHERE clause limits which rows get updated.

Examples

Increase salary by 10% for employees in the Sales department.

MySQL

UPDATE employees
JOIN departments ON employees.dept_id = departments.id
SET employees.salary = employees.salary * 1.1
WHERE departments.name = 'Sales';

Mark orders as shipped for customers in the West region.

MySQL

UPDATE orders
JOIN customers ON orders.customer_id = customers.id
SET orders.status = 'Shipped'
WHERE customers.region = 'West';

Sample Program

This example creates two tables, inserts data, then increases salary by 10% for employees in Sales department. Finally, it shows updated salaries.

MySQL

CREATE TABLE employees (id INT, name VARCHAR(20), dept_id INT, salary DECIMAL(10,2));
CREATE TABLE departments (id INT, name VARCHAR(20));

INSERT INTO employees VALUES (1, 'Alice', 10, 50000), (2, 'Bob', 20, 60000), (3, 'Carol', 10, 55000);
INSERT INTO departments VALUES (10, 'Sales'), (20, 'HR');

UPDATE employees
JOIN departments ON employees.dept_id = departments.id
SET employees.salary = employees.salary * 1.1
WHERE departments.name = 'Sales';

SELECT id, name, salary FROM employees ORDER BY id;

Important Notes

Always back up your data before running UPDATE queries with JOIN to avoid accidental changes.

Make sure the JOIN condition correctly matches rows to update the right records.

You can join multiple tables if needed by adding more JOIN clauses.

Summary

UPDATE with JOIN lets you update one table using data from another table.

Use JOIN to connect tables on common fields and WHERE to filter rows.

It is useful for updating related data efficiently in one query.