How to use Aggregated Subquery to Update Row in MySQL In MySQL

An Aggregated subquery is a subquery that uses aggregation functions and returns a single value.

Example 1: Updating Based on Aggregated Subquery

In this example, a MySQL database schema is created for an ’employees’ table with columns for ‘employee_id,’ ‘department_id,’ and ‘salary.’ Three sample records are inserted to simulate employee data.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    department_id INT,
    salary INT
);
INSERT INTO employees VALUES 
    (1, 1, 50000),
    (2, 2, 60000),
    (3, 1, 55000);

Now consider a scenario where the goal is to update the salary of employees in an ‘employees‘ table based on the average salary of their department.

UPDATE employees
SET salary = (
    SELECT AVG(sub.salary)
    FROM (SELECT * FROM employees) AS sub
    WHERE sub.department_id = employees.department_id
);
SELECT * FROM employees;

Output:

This example calculates the average salary for each department and updates each employee’s salary based on their department’s average, showcasing the power of using subqueries for dynamic updates.

Updating table rows using subqueries in MySQL allows precise modifications based on specific conditions or values derived from other tables.

This guide explains how to update table rows using subqueries in MySQL, with the help of methods, syntax, and working examples that make it easy to understand.