Skip to Content
We are live but in Staging 🎉
Data EnginesSQLAPI ReferenceExecute (SQL)UPDATE

UPDATE

UPDATE through Tables.Execute. The planner picks a route from the predicate’s shape against the table’s merge_keys. For the RPC contract, see the Execute hub.

The response is a row count only — ExecuteResponse.rows_affected, plus statement_kind, max_wal_ulid and warnings. There is no strategy, pending_drain or noop field on the wire.

UPDATE requires a WHERE clause

A bare UPDATE t SET … is refused before it plans:

UPDATE without a WHERE clause is not allowed. Use WHERE TRUEto update every row, orTRUNCATE TABLE to clear the table while keeping the schema.

This is the BigQuery-aligned safety guard: a whole-table rewrite is the most expensive thing the plane exposes and the most common cause of accidental corruption. Say WHERE TRUE if you mean it. Source: dodil-tables/crates/htap-planner/src/dml.rs:159-172.

Keyed updates

Every predicate on a table that declares pk_columns routes through the WAL, then a compactor MERGE on the next drain. Safe under concurrent writes.

KeyedUpdate — point update on the PK

UPDATE events SET event_type = 'click_pricing'
WHERE id = 1 AND user_id = 'u-101';

Composite PKs are handled either as (a, b) IN ((…), (…)) or as a conjunction of = literal conjuncts — one row-tuple per key.

Non-PK conjuncts in the same WHERE become an extra filter applied after the key lookup and before the upsert:

-- keys on id/user_id; `tier = 'pro'` rides along as extra_filter
UPDATE events SET event_type = 'archived'
WHERE id = 1 AND user_id = 'u-101' AND tier = 'pro';

KeyedRange — a range over a single-column PK

UPDATE events SET event_type = 'archived'
WHERE id BETWEEN 1000 AND 2000;

BETWEEN and >= a AND <= b are inclusive; >= a AND < b is exclusive at the top. The plane scans the key range, then writes one WAL entry per key. Ranges over a composite PK are refused rather than guessed (htap-planner/src/ir.rs:171-187).

KeyedFromSubquery — an explicit or synthesised key list

UPDATE events SET event_type = 'archived'
WHERE (id, user_id) IN (
  SELECT e.id, e.user_id
  FROM events e JOIN users u ON e.user_id = u.id
  WHERE u.status = 'inactive'
);

The plane materialises the key list, then dispatches it as a keyed update.

Non-keyed updates — WAL-bypass

This is narrower than it used to be. A non-PK predicate on a keyed table does not bypass the WAL any more. The planner synthesises

SELECT <pk> FROM <target> WHERE <your predicate>

and routes the statement through KeyedFromSubquery, so every write on a keyed table lands in the WAL and cannot be silently clobbered by the compactor’s next drain. The cost is one extra read, proportional to how selective your predicate is — the planner takes that trade deliberately, because correctness beats peak throughput. Source: crates/htap-planner/src/dml.rs:893-925.

So you no longer need “drain first” or “rewrite as a subquery” workarounds. The planner does the second one for you.

NonKeyedUpdate survives for exactly one case: the target declares no pk_columns at all. Such a table has no WAL prefix, so warehouse_update is the only path and there is nothing to race against. The planner attaches a warning naming that reason:

Update routed to warehouse_update — target has no declared pk_columns, so no WAL exists for this table.

It arrives on ExecuteResponse.warnings (htap-planner/src/dml.rs:808-822). Treat it as informational, not as a hazard.

If you want keyed routing, declare a key. Add PRIMARY KEY at CREATE TABLE time, or recreate the table via CTAS with an explicit PRIMARY KEY. A keyless CTAS gets a hidden _rowid key that satisfies the storage layer but will not make your own columns keyed.

Atomic multi-row updates

Keyed DML is the one thing a transaction block accepts, and the block lowers to a single atomic commit:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

On the Postgres wire this is a real buffered session with savepoints. See Execute → One statement per call for what the block will and will not accept.

See also

INSERTDELETE