# Apply inserts, updates and deletes by flag

Join / merge upsert

A variant of [Merge into a table](https://meridian.online/operations/merge-into-a-table).

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

Page: <https://meridian.online/operations/merge-into-a-table/apply-inserts-updates-and-deletes-by-flag>

## Status

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.

## What other tools call it

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

- Azure Data Factory: [`Alter row`](https://learn.microsoft.com/en-us/azure/data-factory/data-flow-alter-row) (read 2026-09-27)
- Apache Hop: [`Synchronize after merge`](https://hop.apache.org/manual/latest/pipeline/transforms/synchronizeaftermerge.html) (read 2026-09-27)
- Pentaho Data Integration: [`Synchronize after merge`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27)