How to Update Table Rows Using Subquery in MySQL

Updating table rows using subqueries in MySQL enables precise modifications based on specific conditions or values from other tables. This technique leverages subqueries within the SET or WHERE clauses of the UPDATE statement, allowing dynamic and context-specific updates.

This guide covers the syntax, methods, and examples of updating rows with aggregated and correlated subqueries, enhancing your database management skills.

Updating Table Rows Using Subqueries in MySQL

Updating table rows using subqueries in MySQL allows for precise, context-specific changes. This method uses subqueries to determine new values or conditions for updating rows, offering a more flexible and advanced approach than 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.

Types of Subqueries

• Aggregated Subquery
  
• Correlated Subquery

Using a Subquery to Update Rows with Aggregated Values 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:

+-------------+---------------+--------+
| employee_id | department_id | salary |
+-------------+---------------+--------+
|           1 |             1 |  52500 |
|           2 |             2 |  60000 |
|           3 |             1 |  52500 |
+-------------+---------------+--------+

Explanation: 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 a Subquery to Update Rows with Correlated Values 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:

+----------+-------------+------------+
| order_id | order_value | status     |
+----------+-------------+------------+
|        1 |      100.00 | Pending    |
|        2 |      150.00 | High Value |
|        3 |      120.00 | Shipped    |
+----------+-------------+------------+

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.

sourabhsahu33

Follow

Improve

Article Tags :

• Databases
• MySQL
• MySQL Query