How to use Correlated Subquery to Update Row in MySQL 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:

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.

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.