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

MERGE

MERGE INTO ... USING ... ON ... WHEN MATCHED ... WHEN NOT MATCHED ... — explicit upsert semantics. K3 picks one of three strategies based on the source shape. For the RPC contract and full enum reference, see the Execute hub.

MERGE_ROWS — source is 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);

Response: strategy: WRITE_STRATEGY_MERGE_ROWS, rowsWritten: "2" (one log append per source row).

MERGE_QUERY — source is a SELECT

-- Merge today's events from a staging table
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);

Response: strategy: WRITE_STRATEGY_MERGE_QUERY.

MERGE_TABLE — source is another table by name

-- Merge an entire staging table — equivalent to MERGE_QUERY with `SELECT * FROM events_staging`,
-- but the planner can short-circuit metadata lookups on the source.
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);

Response: strategy: WRITE_STRATEGY_MERGE_TABLE.

WHEN MATCHED THEN DELETE

Both UPDATE and DELETE actions are supported in WHEN MATCHED clauses:

-- Drop matched rows entirely (e.g. soft-delete inversion)
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;

Pre-drain MERGE response semantics

rows_written on MERGE always counts source rows accepted into the write log — pre-drain we cannot tell inserts apart from updates. The post-drain insert/update/delete classification comes back via DescribeTable.last_drain_*:

dodil k3 table describe events --bucket kb-prod -o json | jq '{
  lastDrainInsertedRows, lastDrainUpdatedRows, lastDrainDeletedRows
}'

See also

DELETEMaterialize