CertGrid CertGrid
Hands-on Lab·MySQL

MySQL UPDATE and DELETE

Change a row by reading its current value in the same statement, then remove one. Both start with the `SELECT` that proves the `WHERE` clause matches what you think it does.

Schemas and Data Guide 8 of 45 Beginner

Written against the versions above. Nothing here is version-specific. The habit in step one is the whole guide.

Every command on this page ran on db-a01.
Server NameIP AddressOSRolesCPURAMHDD
db-a01192.168.0.81Ubuntu 26.04 LTSPrimary / Source / Replica Set Member 12 Core4 GB50 GB

Before you start

  1. Update a value using the value it already has

    SET credit = credit + 10 reads and writes in one statement. The arithmetic happens on the server, atomically, so two concurrent updates cannot both read the same starting figure and lose one of the increments.

    Doing this in application code - select, add ten, update - is the classic lost update, and it is invisible in testing because it needs two requests at once.

    Grace started on 0.00, the column default from the first insert.

    bash 
    sudo mysql appdb -e "UPDATE customers SET credit = credit + 10 WHERE email = 'grace@example.com'"sudo mysql appdb -e "UPDATE customers SET credit = credit + 10 WHERE email = 'grace@example.com'"

    Expected resultNo output from the update, then Grace's credit reading 10.00.

    Success conditionYou can modify a value relative to itself without a read-modify-write race.

  2. Delete one row, and confirm which

    DELETE with a WHERE on the primary key, then the same SELECT again to prove what changed.

    The habit worth building is running the SELECT before the DELETE with the identical WHERE clause. It costs one command and it is the only way to know the clause matches what you intend. A DELETE with a mistyped WHERE is not undoable outside a transaction or a backup.

    Row 4 was the row that failed to insert earlier; row 5 is the Unicode one, which survives.

    bash Example session 
    sudo mysql appdb -e "DELETE FROM customers WHERE id = 4"sudo mysql --table appdb -e "SELECT id, name FROM customers"+----+---------------------+| id | name                |+----+---------------------+|  1 | Ada Lovelace        ||  2 | Grace Hopper        ||  3 | Katsuko Saruhashi   ||  5 | Zoë Washburne 🚀      |+----+---------------------+

    Expected resultThe remaining rows, without id 4.

    Success conditionYou have removed a specific row and verified the result.

  3. Ask how many rows an update actually changed

    ROW_COUNT() reports the effect of the statement before it. Run the same UPDATE twice and the answer differs.

    The first run sets status to suspended on every customer with zero credit. The second run matches the same rows and reports 0, because they already hold that value. MySQL counts rows *changed*, not rows *matched*.

    That distinction is why "0 rows affected" on a migration is not proof the WHERE was wrong - it may mean the work was already done. Which of the two it is decides whether you investigate or move on.

    bash Example session 
    sudo mysql appdb -e "UPDATE customers SET status = 'suspended' WHERE credit = 0; SELECT ROW_COUNT() AS rows_changed"rows_changed1sudo mysql --table appdb -e "SELECT id, name, status, credit FROM customers"+----+---------------------+-----------+--------+| id | name                | status    | credit |+----+---------------------+-----------+--------+|  1 | Ada Lovelace        | active    | 120.50 ||  2 | Grace Hopper        | active    |  10.00 ||  3 | Katsuko Saruhashi   | active    |  75.25 ||  5 | Zoë Washburne 🚀      | suspended |   0.00 |+----+---------------------+-----------+--------+sudo mysql appdb -e "UPDATE customers SET status = 'suspended' WHERE credit = 0; SELECT ROW_COUNT() AS rows_changed"rows_changed0

    Expected resultA non-zero count, the updated rows, then 0 from the identical rerun.

    Success conditionYou can tell an update that did nothing from one that matched nothing.

  4. Let the client stop you deleting a whole table

    --safe-updates refuses any UPDATE or DELETE whose WHERE clause does not use a key column. Here the clause filters on status, which is not indexed.

    ERROR 1175, and nothing was deleted. The mode is blunt - it would refuse a perfectly good statement too - and that is the trade. It exists because DELETE FROM customers is valid SQL that empties the table, and because the most expensive incidents start with a WHERE clause that was fine in testing.

    Worth turning on in your own client config for any session against production.

    bash Example session 
    sudo mysql --safe-updates appdb -e "DELETE FROM customers WHERE status = 'suspended'" ; echo "exit=$?"ERROR 1175 (HY000) at line 1: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.exit=1

    Expected resultERROR 1175 (HY000): You are using safe update mode and exit=1.

    Success conditionYou have a client-side guard against an unqualified delete.

  5. Undo a delete, properly

    The real answer to "I deleted the wrong rows". Four statements in one connection: begin, delete every order, count, roll back, count again.

    after_delete is 0 and after_rollback is 3. The rows were genuinely gone as far as this session was concerned, and ROLLBACK brought all three back. Nothing else on the server ever saw them missing.

    This is InnoDB being transactional, and it is the habit that makes destructive work survivable: START TRANSACTION, run the statement, check the count, and only then COMMIT. Note it must be one connection - a new mysql invocation is a new session and an open transaction would be rolled back on disconnect.

    bash Example session 
    sudo mysql appdb -e "START TRANSACTION; DELETE FROM orders; SELECT COUNT(*) AS after_delete FROM orders; ROLLBACK; SELECT COUNT(*) AS after_rollback FROM orders"after_delete0after_rollback3

    Expected resultafter_delete 0, then after_rollback 3.

    Success conditionYou can make a destructive statement reversible before you run it.

Troubleshooting

Official sources

← All MySQL guides