How Does UPDATE Change Rows Safely?

An UPDATE finds every row that matches the WHERE clause and replaces the columns you list in the SET clause. The expressions on the right-hand side of the SET are evaluated against the current row, so an expression can refer to the column's old value and build on it.

CREATE TABLE tickets (
  id INTEGER PRIMARY KEY,
  title VARCHAR(160) NOT NULL,
  status VARCHAR(20) NOT NULL,
  priority INTEGER NOT NULL,
  attempts INTEGER NOT NULL DEFAULT 0
);

INSERT INTO tickets (id, title, status, priority) VALUES
  (1, 'Login page times out', 'open', 3),
  (2, 'Invoice PDF is blank', 'open', 1),
  (3, 'Export times out',    'open', 2);

UPDATE tickets
SET status = 'in_progress',
    priority = priority - 1,
    attempts = attempts + 1
WHERE id = 3;

SELECT id, title, status, priority, attempts FROM tickets ORDER BY id;

Three rows come back. Ticket 3 now has status 'in_progress', priority 1 and attempts 1; the other two are untouched. The priority - 1 expression read the old value of 2 and stored 1, and attempts + 1 read 0 and stored 1.

Two rules about the SET expressions. All the right-hand sides are evaluated against the original row before any of them are written — the statements are not applied one at a time, so the order of the assignments within the SET clause does not matter. And a column you do not mention keeps its value, so an UPDATE that changes one column leaves the others alone.

The WHERE clause is the safety mechanism, and its absence is the single most common cause of a data incident. Without it, every row in the table is updated. That is not a syntax problem and it is not caught by the database; it is caught, if at all, by a human noticing afterwards. Even a WHERE clause on a non-unique column can match more rows than you expected, which is why the affected-row count is worth checking on every UPDATE that touches a production table.

Run the UPDATE with a precise WHERE clause, then ask the database how many rows it changed. SQLite exposes that as the changes() function, which reports the rows modified by the most recent statement.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(200) NOT NULL
);

INSERT INTO customers (id, name, email) VALUES
  (1, 'Ada Lovelace',   '[email protected]'),
  (2, 'Grace Hopper',   '[email protected]'),
  (3, 'Alan Turing',    '[email protected]');

UPDATE customers
SET email = '[email protected]'
WHERE id = 1;

SELECT changes() AS rows_updated;

SELECT id, name, email FROM customers ORDER BY id;

The first result is 1, the number of rows the UPDATE touched, and the second shows only Ada Lovelace's email changed. Grace Hopper and Alan Turing still have their original addresses.

Checking the affected-row count is the habit that turns a mass update into a contained mistake. If the count is not what you expected, the WHERE clause matched something broader than you intended — a soft-deleted flag you forgot about, a NULL that compared unexpectedly, a missing tenant filter — and you have found it before the commit rather than after.

Two dialect notes. SQLite's changes() reports rows modified by the most recently completed INSERT, UPDATE or DELETE, so read it immediately after the statement you are interested in; another data-modifying statement in between will reset it. PostgreSQL's equivalent is the row count returned in the command tag, and its row-level trigger exposes the count through a special variable in the trigger body. MySQL exposes it through the affected_rows function on the client, and it distinguishes rows matched from rows actually changed — a useful distinction, because an UPDATE that sets a column to the value it already holds reports zero changed rows while still matching the row.

For an even safer pattern, make the WHERE clause as narrow as it can be. Filtering on the primary key is the strongest option; adding a condition on the column's current value — WHERE id = 1 AND email = '[email protected]' — makes the update fail to match if somebody else changed the row in the meantime, which is an optimistic-locking check you get for free.

Safety comes from five layers, and the useful thing is that they stack.

The first layer is the WHERE clause itself. Make it as narrow as the requirement allows: filter on the primary key where you can, and include every predicate that scopes the change — tenant id, account id, a status flag that marks the row as still eligible. A WHERE clause on a non-unique column is not enough, and a WHERE clause that omits a tenant filter is the classic multi-tenant data loss bug.

The second is checking the affected-row count before you commit. Wrap the UPDATE in a transaction, look at the count, and roll back if it is not what you expected. That converts an unrecoverable mistake into a recoverable one, and it takes five lines.

The third is making the update idempotent. If the statement sets a column to an absolute value rather than incrementing it, running it twice produces the same result. An incrementing statement run twice does not, which is why SET attempts = attempts + 1 needs a guard — add a predicate that checks the row is in the expected state, so the second run matches nothing.

The fourth is the database's own protections. MySQL has a safe-updates mode that refuses an UPDATE or DELETE without a WHERE clause unless you explicitly key it. PostgreSQL does not have that built in, but you can add a rule that aborts statements matching a pattern, or revoke UPDATE rights on the table from the application role and give it rights on a view instead. SQL Server has the same safe-update setting as MySQL. These are defence in depth: the guard that catches the mistake you did not think of.

The fifth is the application layer. A dedicated console or migration tool that requires you to type the table name, or a dry-run mode that prints the affected rows without changing anything, catches the human error before the statement reaches the database.

And one habit that beats all of them: run the SELECT first. SELECT id, email FROM customers WHERE ... with the same predicate as the UPDATE shows you exactly which rows will change. It costs a millisecond and it is the last chance to notice that the predicate is wrong.