UPDATE ​

UPDATE assigns new values to existing rows. Its expressions can read the row's old values, so an assignment such as quantity = quantity - 1 adjusts the current quantity.

The update is applied immediately as part of the current transaction. New values must satisfy column types and constraints. Without WHERE, every row is updated.

Syntax ​

UPDATE [OR conflict_policy] table
SET column = expression [, ...]
[FROM sources]
[WHERE condition]
[RETURNING expression, ...]

FROM supplies related rows for a joined update. RETURNING exposes values after the update. Use transaction controls to group the change with other statements.

Examples ​

Adjusting a quantity ​

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

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

1,4

Updating from another source ​

CREATE TABLE adjustments(id INTEGER, delta INTEGER);
INSERT INTO adjustments VALUES (1, 2), (2, -1);
UPDATE stock
SET quantity = stock.quantity + adjustments.delta
FROM adjustments
WHERE stock.id = adjustments.id;

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

1,6
2,2

See INSERT INTO for upserts and transactions for explicit commit and rollback.