A MySQL UPDATE changes every row that matches its WHERE clause, and without a WHERE clause it changes every row in the target table.
The syntax is easy; the production skill is proving the target set, controlling locks, and making the change recoverable before one statement touches millions of rows.
Table of contents
- Start with a preview
- Use transactions and verify affected rows
- Update with expressions and joins
- Batch large changes
- Build a rollback plan
- How this fits the rest of the stack
- FAQ
Start with a preview
SELECT id, status
FROM orders
WHERE status = 'pending' AND created_at < '2026-01-01';
UPDATE orders
SET status = 'archived', updated_at = NOW()
WHERE status = 'pending' AND created_at < '2026-01-01';Run the SELECT with the exact predicate first, inspect representative rows, and count the match set. Keep the predicate sargable so an index can narrow the work. A vague condition that forces a full scan also holds locks longer and creates more replication traffic.
In application code, bind values through parameters rather than concatenating SQL. Parameterization protects syntax and data boundaries; it does not rescue a logically wrong predicate, so tests must still cover the target set.
Use transactions and verify affected rows
START TRANSACTION;
UPDATE accounts SET plan = 'standard' WHERE id IN (101, 102, 103);
SELECT ROW_COUNT();
-- inspect results
COMMIT;A transaction creates a decision point before commit, but it is not permission to run an unbounded update. Large transactions retain locks, generate substantial undo and binary-log work, and make rollback expensive. Verify ROW_COUNT against the expected range and rollback when the number is surprising.
Test the exact statement against a realistic staging copy. Production-only data skew can turn a fast test into a long lock queue, so examine the plan and index coverage too.
Update with expressions and joins
SET expressions can reference current column values, while multi-table updates can derive values from related rows. Keep join relationships explicit and ensure the join does not accidentally multiply matches. MySQL updates each matching target row once, but a confusing join still makes review and reasoning difficult.
UPDATE orders AS o
JOIN customers AS c ON c.id = o.customer_id
SET o.region = c.region
WHERE o.region IS NULL AND c.region IS NOT NULL;Preview the same join as SELECT, include target primary keys, and inspect duplicates. For migrations, a temporary mapping table with constraints is often safer than embedding a dense transformation in one statement.
Batch large changes
Break a large update into primary-key ranges or another stable indexed cursor. Commit between batches, record progress, and make the operation idempotent so it can resume after failure. OFFSET is a poor cursor for changing data because rows move relative to the offset.
Watch lock waits, replica lag, disk growth, buffer pressure, and application latency. Pause when the database is under load. A migration that finishes in ten minutes while starving customer traffic is not faster in any useful sense.
Build a rollback plan
- Back up or copy values that cannot be recomputed
- Record the exact target IDs and old values
- Test forward and reverse statements
- Set a maintenance and monitoring window
- Require peer review for broad predicates
- Keep an emergency stop condition based on locks and latency
Safe updates are observable changes with a bounded target and a reversal path. The best guardrail is not a clever SQL mode; it is a workflow that makes an unexpectedly large match impossible to ignore.
Triggers, generated columns, foreign keys, and row-based replication can make the effect larger than the visible SET clause. Review them before estimating duration or rollback size. In applications, protect concurrent edits with version columns or an expected old value in the WHERE predicate; otherwise a background update can overwrite a user’s newer change. Re-run the preview after deployment and preserve an audit record of who approved the target set.
How this fits the rest of the stack
Database changes consume storage, I/O, and operational headroom. The RunxBuild hosting calculator shows the database beside its application service, and the RunxBuild dashboard keeps deploy and runtime logs available during a migration.
Useful related references:
- MySQL to MySQL: Migrating a Database Between Servers
- MySQL Pivot: There Is No PIVOT, So Here Is What to Do Instead
- mysql -u root -p: What the Flags Mean and Why You Should Stop Using Root
- Databases on RunxBuild
FAQ
What happens if UPDATE has no WHERE clause?
Every row in the target table is eligible for modification. Preview the target and require review before broad updates.
Can I rollback a MySQL UPDATE?
Yes when the table and operation are transactional and you have not committed. After commit, use backups or a prepared reverse change.
How do I know how many rows changed?
Check the client affected-row count or SELECT ROW_COUNT immediately after the statement in the same session.
Can UPDATE use a JOIN?
Yes. Preview the identical join with a SELECT and verify target primary keys before updating.
How should I update millions of rows?
Use stable indexed batches, commit between them, monitor load and replica lag, and make the operation resumable.