MySQL 8: UPDATE on a SELECT query in multiple tables

Introduction

Managing data across multiple tables is a common necessity in many databases, and MySQL is no different. In this guide, we will delve into how to perform an UPDATE using a SELECT query across multiple tables within MySQL 8. This powerful technique allows you to change records in one table based on values in other tables, combining data retrieval with data modification in one step.

Before proceeding, please ensure you have MySQL 8 or later installed on your system, as well as necessary permissions to create databases, tables, and run both SELECT and UPDATE queries. Additionally, familiarity with JOINs in SQL will be beneficial for understanding this tutorial.

Understanding JOINs in UPDATE statements

MySQL allows you to use JOIN clauses in an UPDATE statement to reference columns in multiple tables. This can be a crucial feature when you need to update records in a table based on the content of another table.

To demonstrate, let’s start with a basic example. We have two tables: employees and department_info.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT
);

CREATE TABLE department_info (
    department_id INT,
    manager_id INT
);

Suppose we want to update the manager_id in department_info based on a value from the employees table. Here’s how you might write that:

UPDATE department_info 
JOIN employees 
ON department_info.department_id = employees.department_id 
SET department_info.manager_id = employees.employee_id 
WHERE employees.name = 'John Smith';

The key takeaway is using the JOIN clause to access values across multiple tables within an UPDATE statement.

Using Aliases for Clarity

In more complex queries, using table aliases helps maintain clarity. Here’s the previous example with aliases:

UPDATE department_info di 
JOIN employees e 
ON di.department_id = e.department_id 
SET di.manager_id = e.employee_id 
WHERE e.name = 'John Smith';

Aliases shorten the overall query length and make it more readable, especially when dealing with tables with long names or multiple JOIN operations.

Combining Multiple JOINs

Now let’s tackle a more advanced scenario where updates must be made using data from multiple joins:

UPDATE employees e 
JOIN department_info di ON e.department_id = di.department_id 
JOIN roles r ON e.role_id = r.role_id 
SET e.salary = e.salary * r.salary_multiplier 
WHERE di.manager_id = 1023 AND r.requires_update = 1;

This query illustrates how to use multiple JOIN clauses in an UPDATE statement. The salary of employees is being updated based on their role’s salary multiplier, but only for a specific department manager and roles flagged for updates.

Subqueries with UPDATE

At times, a direct JOIN isn’t the best approach, especially if the relationship between the tables is complex or if aggregation is needed. Subqueries can be used in such cases:

UPDATE employees e
SET e.salary = (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e.department_id)
WHERE e.department_id IN (SELECT department_id FROM department_info WHERE manager_id = 1023);

This example updates the salary of employees to the average salary of their department, but only for departments managed by a specific manager.

Handling Complex Conditions

For intricate criteria, a combination of JOINs, subqueries, and temporary tables may be used to isolate the desired data pattern for an update. Don’t forget that each situation is unique and calls for a tailored approach to structuring your query.

Let’s consider an update that needs to account for multiple conditional checks:

UPDATE employees e 
JOIN department_info di ON e.department_id = di.department_id 
JOIN roles r ON e.role_id = r.role_id 
SET e.salary = e.salary * r.salary_multiplier 
WHERE (di.manager_id = 1023 AND r.requires_update = 1) 
  OR (di.department_id IN (SELECT department_id FROM projects WHERE budget_overrun = 1));

The WHERE clause now includes an additional OR condition that checks against another table, projects, to narrow down the records further.

Conclusion

Using UPDATE with SELECT to manipulate data across multiple tables is a staple of complex SQL operations. Experimenting and understanding the capability of MySQL will result in efficient and powerful queries.