Introduction

Updating many rows with different values in one MySQL statement is a common optimization for batch jobs. Doing this in a single query reduces round trips and keeps updates transactional. The standard technique uses a CASE expression inside UPDATE.

Use CASE for Multi-Value Row Updates

A CASE block lets each row receive a different value based on a key.

1UPDATE products
2SET price = CASE id
3    WHEN 101 THEN 19.99
4    WHEN 102 THEN 24.50
5    WHEN 103 THEN 17.75
6    ELSE price
7END
8WHERE id IN (101, 102, 103);

This updates selected rows in one statement while leaving others unchanged.

Update Multiple Columns Together

You can apply separate CASE expressions for each column.

1UPDATE users
2SET
3    status = CASE user_id
4        WHEN 1 THEN 'active'
5        WHEN 2 THEN 'suspended'
6        WHEN 3 THEN 'active'
7        ELSE status
8    END,
9    quota = CASE user_id
10        WHEN 1 THEN 100
11        WHEN 2 THEN 10
12        WHEN 3 THEN 200
13        ELSE quota
14    END
15WHERE user_id IN (1, 2, 3);

Bundling these changes keeps data state consistent.

Drive Updates from Temporary Table

For larger batches, create a temporary table and join it in one update.

1CREATE TEMPORARY TABLE updates (
2    user_id INT PRIMARY KEY,
3    status VARCHAR(20),
4    quota INT
5);
6
7INSERT INTO updates (user_id, status, quota) VALUES
8(1, 'active', 100),
9(2, 'suspended', 10),
10(3, 'active', 200);
11
12UPDATE users u
13JOIN updates x ON u.user_id = x.user_id
14SET u.status = x.status,
15    u.quota = x.quota;

This approach is easier to generate from application code when many rows are involved.

Transaction and Safety Practices

Wrap large updates in transactions and verify affected row counts. Use a SELECT preview before executing updates in production.

1START TRANSACTION;
2
3-- Run update statement here
4
5SELECT ROW_COUNT() AS changed_rows;
6COMMIT;

For critical tables, test the query against staging data first.

Performance Considerations

Large single updates can lock many rows. Chunk updates by key ranges if lock contention is high. Add indexes on join keys and filter columns to avoid full table scans. Monitor execution plans when batch size grows.

Well-structured batch updates improve both speed and consistency.

Generate CASE Updates from Application Data

When updates come from application memory, generate SQL safely with parameterized execution. This avoids manual query mistakes and reduces injection risk.

1# Example pseudo-builder for demonstration purposes.
2updates = [
3    (101, 19.99),
4    (102, 24.50),
5    (103, 17.75),
6]
7
8case_parts = " ".join([f"WHEN {row_id} THEN {price}" for row_id, price in updates])
9ids = ", ".join([str(row_id) for row_id, _ in updates])
10sql = f"""
11UPDATE products
12SET price = CASE id {case_parts} ELSE price END
13WHERE id IN ({ids})
14"""
15
16print(sql)

In production, bind values through your database driver instead of string interpolation.

Validation Query Before Commit

For critical data, inspect intended changes with a join preview before running update.

SELECT u.user_id, u.status AS old_status, x.status AS new_status
FROM users u
JOIN updates x ON u.user_id = x.user_id;

Reviewing a preview query reduces risk of accidental bulk mistakes.

Auditing and Rollback Strategy

Log batch identifiers, row counts, and execution timestamps for each update run. If a batch behaves unexpectedly, rollback and replay from audit records. Operational safeguards are as important as SQL syntax in production systems.

Clear batch boundaries and preview checks make large update operations much safer in production environments.

Common Pitfalls

• Omitting WHERE filters and updating unintended rows.
• Using very large CASE statements without batching.
• Forgetting transaction boundaries for multi-step batch updates.
• Not indexing keys used in update joins.

Summary

• Use CASE in UPDATE for row-specific values in one query.
• For large batches, prefer temporary-table join updates.
• Wrap updates in transactions and verify row counts.
• Tune indexing and batch size for predictable performance.