How does an UPDATE change rows, and what do the expressions in the SET clause actually see?
An update finds the rows that match the condition and gives each of them new values.
CREATE TABLE tickets (
id INTEGER PRIMARY KEY,
title VARCHAR(160) NOT NULL,
status VARCHAR(20) NOT NULL,
updated_at TEXT
);
UPDATE tickets
SET status = 'closed',
updated_at = '2026-02-01'
WHERE id = 42;
So what do the expressions in the set clause actually see? This is the part that surprises people. All of them see the values that the row had before the update. The engine works out all the new values first, and then it writes the row once.
-- This swaps the two values.
UPDATE accounts SET balance_cents = last_balance_cents,
last_balance_cents = balance_cents
WHERE id = 1;
-- Here is what happened. The first line sets the balance to the old
-- last balance. The second line sets the last balance to the old
-- balance. Both of them used the values from before the update.
So that is why the swap works. It looks impossible, and it is not, because the engine took a copy of the row first.
There is one more thing to know about the set clause. You cannot put a value in a column that is part of the key of the table in some engines, and you should never change a primary key that other tables point at, because nothing will follow the change.
The next two questions in this topic look at a safe update and at how to make one.