UPDATE & DELETE

UPDATE

UPDATE users SET role = 'admin' WHERE id = 'u1';
UPDATE users SET status = 'inactive' WHERE last_login < '2025-01-01';

DELETE

DELETE FROM users WHERE id = 'u1';
DELETE FROM orders WHERE status = 'cancelled';
TRUNCATE users;

Transactions

BEGIN;
INSERT INTO orders (id, total) VALUES ('o1', 99.99);
UPDATE inventory SET stock = stock - 1 WHERE id = 'item1';
COMMIT;

-- Rollback
BEGIN;
DELETE FROM users WHERE id = 'u1';
ROLLBACK;

-- Savepoints
BEGIN;
SAVEPOINT sp1;
INSERT INTO users (id, name) VALUES ('u1', 'Alice');
ROLLBACK TO sp1;
COMMIT;

Atomic Transfers

-- Fungible (currency, resources)
SELECT TRANSFER('wallets', 'player-A', 'player-B', 'gold', 500);

-- Non-fungible (unique items)
SELECT TRANSFER_ITEM('inventory', 'inventory', 'sword-of-doom', 'player-A', 'player-B');

RETURNING

UPDATE and DELETE support a RETURNING clause to get the affected rows back in the same round-trip.

-- UPDATE: returns the post-update image
UPDATE users SET role = 'admin' WHERE id = 'u1' RETURNING *;
UPDATE users SET status = 'inactive' WHERE last_login < '2025-01-01' RETURNING id, status;

-- DELETE: returns the pre-delete image
DELETE FROM orders WHERE id = 'o1' RETURNING *;
DELETE FROM orders WHERE status = 'cancelled' RETURNING id, total;

RETURNING * projects all columns. RETURNING col1, col2 projects named columns. Arithmetic expressions in RETURNING are not supported — bare column names and * only.

Works in both simple-query and extended-query (prepared statement) protocols.

UPDATE … FROM

UPDATE … FROM updates rows in the target collection based on matching rows in one or more source collections. This is the standard alternative to UPDATE … JOIN syntax.

-- Apply a price increase from a pricing table
UPDATE products p
SET price = pricing.new_price
FROM pricing
WHERE p.id = pricing.product_id;

-- Update orders using data from a users table
UPDATE orders o
SET region = u.region
FROM users u
WHERE o.user_id = u.id;

-- Use a CTE as the source
WITH discounts AS (
    SELECT user_id, 0.10 AS pct FROM promotions WHERE active = true
)
UPDATE orders o
SET total = o.total * (1 - d.pct)
FROM discounts d
WHERE o.user_id = d.user_id;

Constraints:

Exactly one source table (or CTE) is supported.
The WHERE clause must contain an equi-join predicate between target and source (target.col = source.col).
Inline subqueries in FROM are not supported; use a CTE.
FROM source collections cannot themselves contain JOINs.

MERGE

MERGE is a single-statement upsert/delete driven by a source query. It evaluates WHEN MATCHED and WHEN NOT MATCHED clauses to decide what to do with each row from the source.

MERGE INTO orders AS target
USING staging_orders AS source ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET total = source.total, status = source.status
WHEN NOT MATCHED THEN
    INSERT (id, user_id, total, status)
    VALUES (source.id, source.user_id, source.total, source.status);

WHEN NOT MATCHED BY SOURCE

Optionally delete (or update) target rows that have no corresponding source row:

MERGE INTO inventory AS target
USING new_stock AS source ON target.sku = source.sku
WHEN MATCHED THEN
    UPDATE SET qty = source.qty
WHEN NOT MATCHED BY TARGET THEN
    INSERT (sku, qty) VALUES (source.sku, source.qty)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

MERGE clause kinds

Clause	Fires when	Allowed actions
`WHEN MATCHED`	Source row matches a target row	`UPDATE SET …`, `DELETE`
`WHEN NOT MATCHED [BY TARGET]`	Source row has no corresponding target row	`INSERT`
`WHEN NOT MATCHED BY SOURCE`	Target row has no corresponding source row	`UPDATE SET …`, `DELETE`

Each clause may include an optional AND <predicate> condition:

MERGE INTO accounts AS t
USING updates AS s ON t.id = s.id
WHEN MATCHED AND s.balance > 0 THEN
    UPDATE SET balance = s.balance
WHEN MATCHED AND s.balance <= 0 THEN
    DELETE
WHEN NOT MATCHED THEN
    INSERT (id, balance) VALUES (s.id, s.balance);

Limitations

UPDATE/DELETE … JOIN syntax is not supported. Use UPDATE … FROM or a subquery: DELETE FROM orders WHERE user_id IN (SELECT id FROM users WHERE ...)
UPDATE/DELETE on timeseries collections is not supported (append-only). Use retention policies.