Update and delete rows ​

UPDATE and DELETE change rows immediately. Use a transaction when several changes should succeed or roll back together.

This example reduces the stock of one item and removes an item that has no stock.

Prepare sample data ​

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

Change matching rows ​

BEGIN;
UPDATE stock SET quantity = quantity - 1 WHERE id = 1;
DELETE FROM stock WHERE quantity = 0;
COMMIT;

WHERE determines which rows change. Omitting it would update or delete every row. COMMIT keeps the transaction's changes; use ROLLBACK instead to discard them.

Verify the result ​

SELECT id, quantity
FROM stock
ORDER BY id
FORMAT csv;

1,4

To read changed rows directly from a write, use its RETURNING clause. See UPDATE, DELETE and transactions.