How to Update Data in a MySQL Table

Of all the DML statements I write, UPDATE is the one I treat with the most respect, because a well-intentioned update without a proper WHERE clause has taken down more than one system I’ve heard about from colleagues — and I’ve personally had a couple of close calls I learned hard lessons from. In this guide, I’ll cover updating data safely and efficiently, along with the internal locking behavior that makes concurrent updates in MySQL actually work.

What Happens Internally During an UPDATE

When I run an UPDATE on an InnoDB table, the engine doesn’t simply overwrite the old row in place. Instead:

1. InnoDB locates the target row(s) using an index if available, or a full table scan if not.
2. It acquires an exclusive row lock on each matching row to prevent conflicting concurrent writes.
3. The old version of the row is preserved in the undo log, which supports both rollback and MVCC (Multi-Version Concurrency Control) so other transactions can still read a consistent snapshot.
4. The change is written to the redo log first (write-ahead logging), then applied to the actual data page.
5. On commit, the lock is released; on rollback, InnoDB uses the undo log to restore the original values.

sequenceDiagram
    participant App
    participant InnoDB
    participant UndoLog as Undo Log
    participant RedoLog as Redo Log

    App->>InnoDB: UPDATE orders SET status='shipped' WHERE order_id=101
    InnoDB->>InnoDB: Acquire exclusive row lock
    InnoDB->>UndoLog: Save old row version (MVCC + rollback)
    InnoDB->>RedoLog: Write new row change (WAL)
    InnoDB-->>App: Query OK, 1 row affected

Step 1: A Basic UPDATE Statement

UPDATE customers
SET email = 'ayesha.new@example.com'
WHERE id = 1;

Output:

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

I always pay attention to the “Rows matched” versus “Changed” counts — if a row matches but the new value equals the old value, MySQL reports it as matched but not changed.

Step 2: Updating Multiple Columns at Once

UPDATE orders
SET status = 'shipped', updated_at = NOW()
WHERE order_id = 205;

Step 3: Updating Multiple Rows With a Condition

UPDATE products
SET price = price * 1.10
WHERE category_id = 5;

I use expressions referencing the current column value like this constantly — for applying percentage-based price increases, incrementing counters, or adjusting stock levels.

UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE product_id = 42 AND stock_quantity > 0;

That last condition, AND stock_quantity > 0, is a habit I picked up after a nasty bug where stock counts went negative under concurrent checkout requests — a small guard clause like this prevents a whole class of race-condition bugs.

Updating With a JOIN

UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.status = 'flagged'
WHERE c.email LIKE '%@suspicious-domain.com';

This is one of MySQL’s most useful non-standard-SQL extensions, and I use it whenever an update depends on data from a related table rather than the table being updated.

Conditional Updates With CASE

UPDATE products
SET price = CASE
    WHEN category_id = 1 THEN price * 1.15
    WHEN category_id = 2 THEN price * 1.05
    ELSE price
END;

I reach for this pattern when different rows need different update logic in a single pass, rather than issuing multiple separate UPDATE statements.

Using Transactions for Safe Multi-Step Updates

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

COMMIT;

I never perform a “transfer” style update like this outside a transaction, because if the second statement fails after the first succeeds, I’d end up with money vanishing rather than moving — a scenario I test explicitly with rollback simulations before shipping financial logic.

Testing an UPDATE Safely Before Running It

This is a habit that has saved me more than once. Before running an update on a production table, I first run the equivalent SELECT with the same WHERE clause to confirm exactly which rows will be affected:

SELECT * FROM customers WHERE email LIKE '%@oldcompany.com';

Only once I’ve confirmed the row count and content look right do I convert it into an UPDATE:

UPDATE customers SET email = REPLACE(email, '@oldcompany.com', '@newcompany.com')
WHERE email LIKE '%@oldcompany.com';

A Real-World Scenario: Bulk Price Adjustment During a Sale

For a retail client running a seasonal promotion, I needed to discount an entire product category temporarily, then revert it after the sale ended. My approach:

-- Step 1: Store original prices for safe rollback
CREATE TABLE price_backup_2026_sale AS
SELECT product_id, price FROM products WHERE category_id = 3;

-- Step 2: Apply the discount
UPDATE products
SET price = ROUND(price * 0.80, 2)
WHERE category_id = 3;

-- Step 3 (after the sale ends): Restore original prices
UPDATE products p
JOIN price_backup_2026_sale b ON p.product_id = b.product_id
SET p.price = b.price;

Backing up the affected rows into a temporary table before a bulk update is something I do routinely for any update that touches pricing or financial data, since it gives me an exact, guaranteed rollback path beyond just relying on transaction logs.

Locking Behavior and Concurrency

I check the current isolation level with:

SELECT @@transaction_isolation;

MySQL’s default, REPEATABLE READ, combined with InnoDB’s next-key locking, is usually the right balance between consistency and concurrency for most applications I build.

Security Considerations When Updating Data

• I use parameterized queries in application code, never raw string concatenation, to prevent SQL injection through update statements.
• I enforce least-privilege database accounts — reporting tools get SELECT only, application services get scoped UPDATE on specific tables, never blanket privileges.
• I log critical updates (financial transactions, permission changes) to an audit table via triggers, so I have a full history independent of application-level logging.

CREATE TRIGGER trg_orders_audit
AFTER UPDATE ON orders
FOR EACH ROW
INSERT INTO orders_audit_log (order_id, old_status, new_status, changed_at)
VALUES (OLD.order_id, OLD.status, NEW.status, NOW());

Troubleshooting Common Update Issues

Issue: “You are using safe update mode”

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

MySQL Workbench and the CLI client both have a “safe updates” mode enabled by default to protect me from exactly the kind of accidental mass-update disaster I mentioned earlier. I either add a proper WHERE clause using an indexed column, or, if I genuinely intend a full-table update, I temporarily disable it:

SET SQL_SAFE_UPDATES = 0;

Issue: Deadlock detected

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

I resolve this by ensuring my application always acquires locks on multiple tables/rows in a consistent order across all code paths, and I add retry logic for deadlock errors since they’re a normal, expected part of highly concurrent systems.

Issue: Update runs but affects zero rows unexpectedly

I re-run the equivalent SELECT with the same WHERE clause to double check my assumptions about what data actually exists.

Performance Best Practices for Updates

• I make sure the WHERE clause uses an indexed column, since an unindexed update triggers a full table scan under an exclusive lock, blocking other writers far longer than necessary.
• I batch very large updates into smaller chunks (e.g., 10,000 rows at a time) to avoid holding long-running locks and bloating the undo log on massive tables.

UPDATE large_table SET processed = 1
WHERE processed = 0
LIMIT 10000;

I run this in a loop until zero rows are affected, which keeps transaction size manageable on very large tables.

• I avoid updating columns that don’t actually need to change, since MySQL still logs and locks rows even when the “changed” count differs from “matched.”

Frequently Asked Questions

Q: Can I update a row and see the number of rows actually changed versus matched? A: Yes, the MySQL client reports both “Rows matched” and “Changed” after every update.

Q: What’s the safest way to test an update before running it in production? A: I always run the equivalent SELECT with the identical WHERE clause first to preview affected rows.

Q: How do I undo an update if I made a mistake? A: If I’m still inside an open transaction, ROLLBACK reverts it instantly. If it was already committed, I restore from a backup table (as in my sale-discount example) or from a full database backup/binlog point-in-time recovery.

Q: Why did my UPDATE with a JOIN fail silently on some rows? A: If the join condition doesn’t match a row in the joined table, that row simply isn’t updated — I always verify join conditions carefully with a SELECT first.

Interview Questions I’ve Encountered

1. Explain what the undo log is used for during an UPDATE in InnoDB.
2. How does MySQL’s default REPEATABLE READ isolation level affect concurrent updates?
3. What causes a deadlock during updates, and how would you prevent it?
4. Why is it dangerous to run UPDATE on a large table without an indexed WHERE clause?
5. How would you safely perform a bulk update on a 100-million-row table in production without causing downtime?

Summary and Key Takeaways

Updating data safely in MySQL comes down to discipline: always preview with a SELECT first, always use transactions for multi-step changes, always make sure the WHERE clause is indexed, and always think about concurrency and locking before running an update against a live production table.

Key takeaways:

• Preview affected rows with SELECT before converting to UPDATE.
• Wrap multi-step or financially sensitive updates in transactions.
• Use UPDATE ... JOIN for cross-table updates instead of subqueries where possible.
• Batch very large updates to avoid long-held locks.
• Enforce least-privilege access and audit logging for sensitive update operations.

References

• MySQL 8.0 Reference Manual, UPDATE Statement: https://dev.mysql.com/doc/refman/8.0/en/update.html
• MySQL InnoDB Locking and Transaction Model: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
• MySQL Transaction Isolation Levels: https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html