UPDATE Best Practices

Writing safe and efficient UPDATE statements requires discipline. A single mistake can corrupt thousands of rows. Following best practices protects your data and makes your SQL easier to maintain.

Key Best Practices

Always use a WHERE clause unless you genuinely intend to update every row.
Use transactions so you can ROLLBACK if the update produces unexpected results.
Preview with SELECT — run the same WHERE clause in a SELECT statement before executing UPDATE.
Limit rows — add LIMIT N to cap the number of rows affected during bulk updates.
Index the WHERE columns — updates on unindexed columns cause full table scans and lock more rows.

Batch Updates for Large Tables

When updating millions of rows, avoid a single large UPDATE that holds a long lock. Instead, update in batches (e.g., 1000 rows at a time) inside a loop. This reduces lock duration and keeps the table accessible.

After large updates, consider running ANALYZE TABLE to refresh statistics so the query optimizer has accurate information.

Example

START TRANSACTION;

-- Preview first
SELECT COUNT(*) FROM orders WHERE status = 'pending' AND created_at < '2024-01-01';

-- Then update
UPDATE orders
SET status = 'expired'
WHERE status = 'pending'
  AND created_at < '2024-01-01'
LIMIT 500;

COMMIT;

SQL

Full Editor

Pro Tip

For bulk updates on large tables, update in small batches to reduce lock contention.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned