Update existing rows transactionally ​

UPDATE changes matching rows immediately in the current transaction. There is no asynchronous mutation completion step. Without WHERE, every row is updated, so preview the intended key set before applying a broad change.

CREATE TABLE inventory_items(id INTEGER PRIMARY KEY, quantity INTEGER);
INSERT INTO inventory_items VALUES (1, 5), (2, 3);

UPDATE inventory_items
SET quantity = quantity - 1
WHERE id = 1
RETURNING id, quantity
FORMAT csv;

The example returns the new quantity of 4. Assignment expressions can read old row values, and the resulting values must still satisfy declared types and constraints.

For a row that may not exist, use INSERT ... ON CONFLICT ... DO UPDATE when a suitable unique key defines the conflict. Decide how repeated requests should behave: subtracting twice is a different contract from setting an absolute value twice.

Wrap related changes in a transaction so the application can commit or roll back the complete operation. Native sessions retain open transactions; HTTP requires every transaction statement in one request. Confirm the committed state with a targeted SELECT rather than interpreting a read-work counter as the number of changed rows.

See UPDATE, upserts and transactions.