Join / merge upsert · All operations

Apply inserts, updates and deletes by flag

A variant of Merge into a table

Insert, update or delete each target row according to a flag set on the incoming row.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Insert, update or delete each target row by the flag on the incoming row

SQL
CREATE TEMP TABLE target AS SELECT * FROM (VALUES (1, 10), (2, 20), (3, 30), (4, 40)) t(id, v);
CREATE TEMP TABLE incoming AS SELECT * FROM (VALUES
  (1, 'U', 11), (2, 'D', NULL), (5, 'I', 50), (3, 'I', 99), (6, 'U', 60), (7, 'D', 0), (NULL, 'I', 5)) t(id, flag, v);
-- MERGE stops if a target row matches two incoming rows: the incoming rows hold one row for each key
CREATE TEMP TABLE repeated_keys AS SELECT id FROM incoming WHERE id IS NOT NULL GROUP BY id HAVING count(*) > 1;
MERGE INTO target t USING incoming s ON t.id = s.id
  WHEN MATCHED AND s.flag = 'D' THEN DELETE
  WHEN MATCHED AND s.flag = 'U' THEN UPDATE SET v = s.v
  WHEN NOT MATCHED AND s.flag = 'I' AND s.id IS NOT NULL THEN INSERT VALUES (s.id, s.v);
SELECT * FROM target ORDER BY id;
SELECT CASE WHEN ((SELECT count(*) = 0 FROM repeated_keys)
  AND (SELECT list((id, v) ORDER BY id) = [(1, 11), (3, 30), (4, 40), (5, 50)] FROM target)) THEN 'as expected' ELSE error('the target should hold ids 1 (updated to 11), 3 (flagged I but already there, so unchanged), 4 and 5 (inserted), with 2 deleted, and the update and delete flags on keys 6 and 7, absent from the target, and the insert flag on a NULL key, should do nothing') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the target should hold ids 1 (updated to 11), 3 (flagged I but already there, so unchanged), 4 and 5 (inserted), with 2 deleted, and the update and delete flags on keys 6 and 7, absent from the target, and the insert flag on a NULL key, should do nothing”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache Hop (1)
  • Synchronize after mergeread 2026-09-27

    Insert, update or delete database rows by the flag Merge rows (diff) produced

Azure Data Factory (1)
  • Alter rowread 2026-09-27

    Mark rows for insert, update, delete or upsert at the sink by condition

Pentaho Data Integration (1)
  • Synchronize after mergeread 2026-09-27

    Insert, update or delete database rows in one pass by the value of a flag field

Background

In Apache Hop, Synchronize after merge carries out inserts, updates and deletes on a database table by the value of a flag column on each incoming row.[1] In Azure Data Factory, the Alter Row transformation sets insert, delete, update and upsert policies on rows, using conditions written as expressions.[2]

When to use it

In Apache Hop, Merge rows (diff) compares a reference stream of previous data with a compare stream of new data and flags each row identical, changed, new or deleted.[3] In Apache Hop, Merge rows (diff) is useful for comparing data collected at two different times, for example when the source system holds no timestamp of its last update.[3]

In Azure Data Factory, each row is marked with the policy of the first condition it matches, so the conditions should be specified in order of priority.[2] In Azure Data Factory, a row that matches no Alter Row condition is marked for insert by default.[2] Decide what each flag should do when the target disagrees with it: an update or delete for a key that is not there, or an insert for a key that already is. (Meridian's view)

The method

In Apache Hop, Synchronize after merge reads the operation from a named field, and by default inserts on the value new, updates on changed and deletes on deleted.[1] In Apache Hop's Merge rows (diff), the output row is based on the reference row when the flag is identical or deleted, and on the compare row when it is new or changed.[3] In Azure Data Factory's data flow script, Alter Row conditions are written as insertIf, updateIf, deleteIf and upsertIf.[2]

Sources

  1. [1]Synchronize after merge | Apache Hopdocumentation
  2. [2]Alter row transformation in mapping data flow - Azure Data Factory & Azure Synapse | Microsoft Learndocumentation
  3. [3]Merge rows (diff) | Apache Hopdocumentation