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

MERGE

MERGE INTO … USING … ON … WHEN MATCHED … WHEN NOT MATCHED … — explicit upsert semantics through Tables.Execute. The planner resolves USING into one of three source shapes. For the RPC contract, see the Execute hub.

The response is a row count only — ExecuteResponse.rows_affected. There is no strategy field on the wire.

Source: inline VALUES

MERGE INTO events AS t
USING (VALUES
  (1, 'u-101', TIMESTAMP '2026-05-27 10:00:00', 'click_pricing', '{"page":"/pricing","variant":"B"}'),
  (4, 'u-103', TIMESTAMP '2026-05-27 10:03:00', 'signup',        '{"plan":"pro"}')
) AS s (id, user_id, occurred_at, event_type, payload)
ON t.id = s.id AND t.user_id = s.user_id
WHEN MATCHED THEN
  UPDATE SET event_type = s.event_type, payload = s.payload
WHEN NOT MATCHED THEN
  INSERT (id, user_id, occurred_at, event_type, payload)
  VALUES (s.id, s.user_id, s.occurred_at, s.event_type, s.payload);

The rows are inlined and passed straight through. rows_affected is 2 — one WAL append per source row.

Source: a subquery

MERGE INTO events AS t
USING (
  SELECT * FROM events_staging
  WHERE occurred_at >= DATE_TRUNC('day', CURRENT_TIMESTAMP)
) AS s
ON t.id = s.id AND t.user_id = s.user_id
WHEN MATCHED THEN UPDATE SET
  event_type = s.event_type,
  payload    = s.payload
WHEN NOT MATCHED THEN INSERT (id, user_id, occurred_at, event_type, payload)
  VALUES (s.id, s.user_id, s.occurred_at, s.event_type, s.payload);

The plane runs the SELECT to materialise source rows first, then dispatches.

Source: another table by name

MERGE INTO events AS t
USING events_staging AS s
ON t.id = s.id AND t.user_id = s.user_id
WHEN MATCHED THEN UPDATE SET event_type = s.event_type, payload = s.payload
WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.user_id, s.occurred_at, s.event_type, s.payload);

Equivalent to the subquery form with a SELECT * FROM events_staging body.

Only these three USING shapes exist. A bare table, a VALUES list, or a SELECT subquery — anything else is refused with “MERGE USING: only bare table, VALUES, or SELECT subquery supported in v1” (dodil-tables/crates/htap-planner/src/dml.rs:758-762).

WHEN MATCHED THEN DELETE

Both UPDATE and DELETE are valid matched actions:

MERGE INTO events AS t
USING events_to_purge AS s
ON t.id = s.id AND t.user_id = s.user_id
WHEN MATCHED THEN DELETE;

What rows_affected counts

Pre-drain, rows_affected counts source rows accepted into the WAL — the plane cannot tell an insert from an update until the compactor merges them (proto/api/tables.proto:172-175: “rows the statement wrote (WAL appends + tombstones; async drain refines insert-vs-update)”).

There is no post-drain insert/update/delete breakdown API. Earlier drafts of this page pointed at DescribeTable.last_drain_* fields; no such fields exist in dodil.tables.v1. What you can observe is the drain itself:

# Force the drain and read what it moved
dodil data table compact events -b kb-prod -o json
# → {"wal_entries_processed": 247, "wal_unique_keys": 189, "drained_high_ulid": "01J…"}

wal_unique_keys < wal_entries_processed is normal — the compactor dedupes multiple writes to the same key (newest wins) before the MERGE.

For standing backlog rather than a single drain, GetDatabaseStats reports wal_segments, wal_entries_estimate, wal_high_ulid and per-table wal_backlog_entries (proto/api/tables.proto:788-823).

See also

DELETEMaintenance