Shape / columns · All operations

Replace values from a mapping table

A variant of Replace values

Take the pairs of old and new values from a second table and apply them to one or more columns.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Replace values with the pairs from a mapping table

It reads the orders table the proofs share. Create it first:

SQL

CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL

CREATE TEMP TABLE mapping AS SELECT * FROM (VALUES
  ('north', 'N'), ('south', 'S'), ('east', 'E'), ('west', 'W'), ('widget', 'wdg'), ('gadget', 'gdg')
) t(old_value, new_value);
CREATE TEMP TABLE result AS
SELECT o.order_id, coalesce(mr.new_value, o.region) AS region, coalesce(mp.new_value, o.product) AS product
FROM orders o
LEFT JOIN mapping mr ON mr.old_value = o.region
LEFT JOIN mapping mp ON mp.old_value = o.product;
SELECT * FROM result ORDER BY order_id;
SELECT CASE WHEN ((SELECT count(*) FROM result) = 10 AND (SELECT list(DISTINCT region ORDER BY region) FROM result WHERE region IS NOT NULL) = ['E', 'N', 'S', 'W'] AND (SELECT count(*) FILTER (WHERE region IS NULL) FROM result) = 1 AND (SELECT list(DISTINCT product ORDER BY product) FROM result) = ['gdg', 'gizmo', 'wdg']) THEN 'as expected' ELSE error('region and product were not recoded from the mapping table, with unmapped values kept and ten rows out') END AS verdict;

It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “region and product were not recoded from the mapping table, with unmapped values kept and ten rows out”.
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).

Alteryx Designer (1)

Dynamic Replaceread 2026-09-27
Replace values across fields by conditions and replacements held in a second table

Apache NiFi (1)

ReplaceTextWithMappingread 2026-09-27
Replace content matched by a regular expression with values from a mapping file

EasyMorph (1)

Replace with lookupread 2026-09-27
Replace substrings in one or many columns using a lookup table of replacements

KNIME Analytics Platform (1)

String Replacer (Dictionary)read 2026-09-27
Replace strings using patterns and replacements looked up in a dictionary table

Talend (1)

tReplaceListread 2026-09-27
Search and replace values in chosen columns using pairs from a lookup flow

Background

In KNIME, the String Replacer (Dictionary) node replaces strings in selected columns, reading the patterns and their replacements from a dictionary table.^[1] In EasyMorph, the Replace with lookup action replaces substrings in one or more columns using a lookup table.^[2] In EasyMorph, Replace with lookup is used to replace common misspellings with the correct values and abbreviations with the full values.^[2]

When to use it

In EasyMorph, the Replace with lookup action makes its replacements in the order they are encountered in the lookup table.^[2] In EasyMorph, one source value may undergo several replacements from a lookup table, and an earlier replacement can stop an expected later one from applying.^[2] In EasyMorph, matching against the lookup table's values is case-sensitive, so The will not match the.^[2] Put longer, more specific entries of a substring mapping table ahead of the shorter entries they contain. (Meridian's view)

In PostgreSQL, an UPDATE with a FROM clause joins the target table to the other tables, and each output row of the join is an update to the target table.^[3] In PostgreSQL, when a target row joins to more than one row of the other tables in an UPDATE with FROM, only one is used, and which one is not readily predictable.^[3] In DuckDB 2.0, a join written as JOIN BY (TYPE single) pairs each left row with its matching right row, or NULL, and throws an error if a left row has more than one match.^[4] Check that a mapping table holds one row per old value before applying it, or apply it with a join that fails when it does not. (Meridian's view)

In Apache NiFi, the ReplaceTextWithMapping processor polls a mapping file on the file system for updates, every 60 seconds by default.^[5]

The method

In DuckDB, a LEFT join returns every row of the left relation at least once, and sets the other table's attributes to NULL in a row that has no match.^[4] Applied as a LEFT join on the old value, a mapping table needs a rule for the rows it leaves without a new value. (Meridian's view)

In EasyMorph, Replace with lookup reads the substrings to find from one column of the lookup table and their replacements from another.^[2] In EasyMorph, the Replace with lookup action differs from the Lookup action by replacing substrings rather than full cell values.^[2] In KNIME's String Replacer (Dictionary) node, a string that no dictionary pattern matches comes out equal to the input string.^[1]

Compared with the alternatives

In Apache Hop, the Value Mapper is a simple alternative to a conversion table in a database, holding one row per mapping of source value and target value in the transform.^[6] Pairs written into the step suit a short list that rarely changes; a separate table suits a long list, or one that others edit. (Meridian's view)

Sources

[1]String Replacer (Dictionary)documentation
[2]REPLACE WITH LOOKUP [EasyMorph Help]documentation
[3]PostgreSQL: Documentation: 18: UPDATEdocumentation
[4]FROM and JOIN Clauses – DuckDBdocumentation
[5]ReplaceTextWithMapping - Apache NiFidocumentation
[6]Value Mapper | Apache Hopdocumentation