How to Update Table Rows Using Subquery in MySQL

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.

Updating Table Rows Using Subqueries in MySQL

In MySQL, updating table rows using subqueries is a robust technique that helps users make targeted and context-specific modifications within a table.

This approach involves leveraging the results of a subquery to determine new values or conditions for updating specific rows, providing a flexible and advanced solution beyond standard update statements.

Syntax

The syntax for updating table rows with subqueries revolves around embedding a subquery within the SET clause or WHERE clause of the UPDATE statement. Here’s the general structure:

UPDATE table_name
SET column_name = (SELECT column FROM other_table WHERE condition)
WHERE EXISTS (SELECT 1 FROM other_table WHERE condition);

This syntax allows you to set the value of a column based on the result of a subquery, providing a dynamic and adaptable updating mechanism.

There are two types of subqueries that we can use to update rows of a table in MySQL:

Aggregated Subquery
Correlated Subquery

Using Aggregated Subquery to Update Row 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:

Using Subquery 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.

Using Correlated Subquery to Update Row in MySQL

A correlated subquery is a query that uses values from outer query.

Example

In this example, a MySQL database schema is created for an ‘orders’ table with columns for ‘order_id,’ ‘order_value,’ and ‘status.’ Three sample records are inserted to simulate orders data.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_value DECIMAL(8, 2),
    status VARCHAR(50)
);

INSERT INTO orders VALUES 
    (1, 100.00, 'Pending'),
    (2, 150.00, 'Processing'),
    (3, 120.00, 'Shipped');

Consider a case where the objective is to update a ‘status‘ column in an ‘orders‘ table based on the maximum order value in the same table.

UPDATE orders
SET status = 'High Value'
WHERE order_value > (SELECT MAX(order_value) FROM orders);
SELECT * FROM orders;

In the provided example, the subquery is employed within the UPDATE statement’s WHERE clause to dynamically determine the rows to be updated based on a specific condition.

The subquery (SELECT MAX(order_value) FROM orders) retrieves the maximum ‘order_value’ from the ‘orders’ table. Rows in the ‘orders’ table where the ‘order_value’ exceeds this maximum value are selected for the subsequent update operation, showcasing the versatility of subqueries in tailoring updates based on calculated conditions.

Output:

Correlated Subquery Output

Explanation: This example utilizes a correlated subquery to update the ‘status‘ column for orders with a value greater than the maximum order value in the table, showcasing the versatility of subqueries in diverse scenarios.

Conclsuion

Subqueries provide dynamic and precise modifications while updating table rows in MySQL. This advanced method improves the updating process in scenarios demanding accuracy and context-specific updates.

By learning these techniques to update table rows unsing subqueries in MySQL, users can enhance the accuracy and relevance of their database updates. Explore the nuanced world of updating table rows with subqueries and elevate your MySQL database management skills.

Article Tags :

Databases

MySQL

MySQL Query