Conditional UPDATE in SQL: Atomic Check-and-Set with WHERE

A conditional UPDATE is a pattern where the precondition lives directly in the WHERE clause, so the check and the write happen in one atomic statement. The textbook case is debiting an account only when funds suffice: the database itself decides the row does not qualify and reports zero affected rows instead of corrupting data.

Check and write in one statement

Take an account and try to debit 200. The naive approach is SELECT balance, compare in application code, then UPDATE. Between those steps another transaction can change the balance, and you spend money that is already gone. The right move is to fuse the check and the write:

UPDATE accounts
SET balance = balance - 200
WHERE id = 1
  AND balance >= 200;

Here balance >= 200 is not a filter for reporting; it is a precondition. If funds are short, the row fails the WHERE and the UPDATE simply changes nothing. There is no separate read: the new value is derived from the current one (balance - 200) inside the engine, under a row lock.

• The condition and the write see the same snapshot of the row; nothing can slip in between.
• balance = balance - 200 reads the live value, not what you saw a moment ago.
• If no row with id = 1 exists, the outcome is the same: zero affected rows.

The affected-row count as a signal

The key trick is not to run a second SELECT after the UPDATE to learn whether the debit went through. The number of changed rows already tells you. Drivers expose it: in PostgreSQL via cmd_status / rowcount, and psql shows the UPDATE 1 or UPDATE 0 tag.

-- 1 row -> debit succeeded; 0 rows -> insufficient funds
UPDATE accounts
SET balance = balance - 200
WHERE id = 1
  AND balance >= 200;

The application logic becomes trivial: got 1, success; got 0, "insufficient funds" (or no such account). To tell those two cases apart, RETURNING is handy:

UPDATE accounts
SET balance = balance - 200
WHERE id = 1
  AND balance >= 200
RETURNING id, balance;

An empty result is a rejection; a single row is success with the new balance, with no extra query.

Compare-and-swap by version

The same pattern fixes the lost-update problem for any field, not just money. Suppose two processes read an order and both want to change it. Put the expected prior value into the WHERE and you have compare-and-swap.

UPDATE orders
SET status = 'shipped'
WHERE id = 42
  AND status = 'paid';

Exactly one of them moves the order from paid to shipped and gets 1 row; the second no longer sees paid and gets 0, its update safely rejected. A version column often backs this:

UPDATE orders
SET status = 'shipped',
    version = version + 1
WHERE id = 42
  AND version = 7;

This is optimistic locking: you do not hold the row locked while you think, you only verify at write time that nobody touched it.

A gotcha and cross-database notes

The big mistake is sliding back to a two-step "SELECT then UPDATE" under load. The gap between the steps is a race window, and under concurrent requests the balance goes negative. The condition must live in the WHERE of the same UPDATE.

• Transactions. Under READ COMMITTED (PostgreSQL's default), the UPDATE re-reads the row before writing, so the pattern is safe even without explicit locks. But if you did a SELECT first and an UPDATE later in one transaction, only the condition in the UPDATE protects you, not what the SELECT saw.
• MySQL/InnoDB. Same behavior; the row count depends on a flag: with CLIENT_FOUND_ROWS the server returns matched rows, otherwise rows actually changed. That matters when the new value equals the old one.
• ClickHouse. ALTER TABLE ... UPDATE is asynchronous and non-transactional, and it gives no guarantee for checks like "never go negative." ClickHouse is not built for strict check-and-set; keep such a balance in an OLTP database.

Remember: one UPDATE, the precondition in WHERE, the decision driven by the affected-row count.