Join / merge upsert · All operations

Update matching rows only

A variant of Merge into a table

Overwrite values in the target rows whose key matches the source, inserting nothing.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Overwrite the target rows whose key matches, and insert nothing

SQL
CREATE TEMP TABLE target AS SELECT * FROM (VALUES (1, 'a', 1), (2, 'b', 2), (3, 'c', 3), (NULL, 'n', 0)) t(id, name, qty);
CREATE TEMP TABLE source AS SELECT * FROM (VALUES
  (1, 'A', 10, 1), (2, 'b1', 20, 1), (2, 'b2', 21, 2), (3, NULL, NULL, 1), (9, 'new', 9, 1), (NULL, 'nk', 5, 1)) t(id, name, qty, seq);
-- MERGE stops if one target row matches two source rows, so the source keeps its latest row for each key (the NULL key matches no target row)
MERGE INTO target t USING (SELECT id, name, qty FROM source QUALIFY row_number() OVER (PARTITION BY id ORDER BY seq DESC) = 1) s ON t.id = s.id
  WHEN MATCHED THEN UPDATE SET name = s.name, qty = s.qty;
SELECT * FROM target ORDER BY id NULLS LAST;
SELECT CASE WHEN ((SELECT count(*) = 4 AND list((id, name, qty) ORDER BY id NULLS LAST) IS NOT DISTINCT FROM [(1, 'A', 10), (2, 'b2', 21), (3, NULL, NULL), (NULL, 'n', 0)] FROM target)) THEN 'as expected' ELSE error('the target should still hold 4 rows: ids 1 and 2 overwritten (2 by its latest source row), id 3 overwritten with NULLs, the NULL-key row untouched and no row for the unmatched keys 9 and NULL') 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 still hold 4 rows: ids 1 and 2 overwritten (2 by its latest source row), id 3 overwritten with NULLs, the NULL-key row untouched and no row for the unmatched keys 9 and NULL”.
  • 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)
  • Updateread 2026-09-27

    Update the database rows that match lookup keys, without inserting

KNIME Analytics Platform (1)
  • Table Updaterread 2026-09-27

    Overwrite cells of a table with those of an update table matched by RowID and column

Pentaho Data Integration (1)
  • Updateread 2026-09-27

    Update rows of a database table by key

Polars (1)
  • DataFrame.updateread 2026-09-27

    Overwrite values with those of another frame, matched on key columns or row position

RapidMiner Studio (2)
  • Update Databaseread 2026-09-27

    Update rows in a database table where the ID columns match the input rows

  • Update MongoDBread 2026-09-27

    Update matching documents in a MongoDB collection

Background

In Apache Hop, the Update transform looks up a table row using one or more lookup keys and updates it if needed, and does nothing if the row cannot be found.[1] In KNIME, the Table Updater node updates cells of an input table with the cells of an update table that have the same column and the same RowID.[2]

When to use it

In KNIME's Table Updater, a missing value in the update table leaves the input value unchanged unless Perform update with missing values is checked.[2] Decide whether an empty value in the update means clear this field or nothing to report, and set the option to match. (Meridian's view)

Polars' DataFrame.update joins on the implicit row index of each frame when no join columns are given.[3] In DuckDB, UPDATE ... FROM uses an arbitrary matching source row, where MERGE INTO throws an error when a target row matches several source rows.[4] Keep one update row per key, chosen by an explicit order, so the value that lands is the one you meant. (Meridian's view) Polars marks DataFrame.update as unstable functionality that may be changed at any point without being considered a breaking change.[3]

The method

In Apache Hop, the Update transform finds rows through a key lookup table of field values and comparators such as =, <>, LIKE and BETWEEN.[1] In Apache Hop, the Update transform can write whether each key was found or not to a flag field.[1] KNIME's Table Updater lets the user control how to deal with update-table columns missing from the input table and with rows whose RowIDs do not occur in it.[2]

Compared with the alternatives

In Apache Hop, the Insert/Update transform inserts a new row when no table row for the keys can be found, and does nothing when the fields to update are the same.[5]

Sources

  1. [1]Update | Apache Hopdocumentation
  2. [2]Table Updaterdocumentation
  3. [3]polars.DataFrame.update — Polars documentationdocumentation
  4. [4]MERGE INTO Statement – DuckDBdocumentation
  5. [5]Insert / Update | Apache Hopdocumentation