Mysql – SQL – Update Multiple Records In One Query

In MySQL, you can update multiple records in a single query using the CASE statement. This allows you to conditionally update different records with different values based on some criteria. Here’s how you can update multiple records in one query:

1. Using CASE Statement

The CASE statement allows you to handle multiple updates in one query. For example, if you want to update the status of several users based on their user_id, you can do this in one query:

UPDATE users
SET status = CASE
    WHEN user_id = 1 THEN 'active'
    WHEN user_id = 2 THEN 'inactive'
    WHEN user_id = 3 THEN 'pending'
    ELSE status
END
WHERE user_id IN (1, 2, 3);

Explanation:

• The UPDATE statement specifies the table (users) that you want to modify.
• The SET clause uses the CASE statement to check each user_id and assigns a new value to the status column.
• The WHERE clause restricts the update to the user_id values 1, 2, and 3.
• ELSE status ensures that if a record doesn’t match any of the conditions, it remains unchanged.

2. Updating Multiple Columns for Different Records

You can also update different columns for different records. For example, if you want to update both status and last_login for multiple users:

UPDATE users
SET 
    status = CASE
        WHEN user_id = 1 THEN 'active'
        WHEN user_id = 2 THEN 'inactive'
        ELSE status
    END,
    last_login = CASE
        WHEN user_id = 1 THEN '2025-01-20'
        WHEN user_id = 2 THEN '2025-01-15'
        ELSE last_login
    END
WHERE user_id IN (1, 2);

3. Using VALUES for Bulk Updates (MySQL 8.0 and above)

If you are using MySQL 8.0 or later, you can update multiple rows with different values using the VALUES keyword.

UPDATE products
JOIN (VALUES 
    (1, 'Product A', 100),
    (2, 'Product B', 200),
    (3, 'Product C', 150)
) AS new_values(id, name, price)
ON products.product_id = new_values.id
SET products.name = new_values.name,
    products.price = new_values.price;

This query updates the name and price columns of the products table for multiple records, matching on product_id.

4. Multiple UPDATE Statements in One Query

In MySQL, you cannot directly execute multiple UPDATE statements in a single query. However, you can run multiple UPDATE statements in a single command by separating them with semicolons:

UPDATE users SET status = 'active' WHERE user_id = 1;
UPDATE users SET status = 'inactive' WHERE user_id = 2;

This will execute both UPDATE queries sequentially.