Shape / columns · All operations

Overwrite a column with a formula

A variant of Add a calculated column

Replace the values of an existing column with the result of an expression or another column's values, adding no column.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Replace a column in place with a formula

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 result AS
SELECT * REPLACE (round(amount * 1.2, 2) AS amount) FROM orders;
SELECT order_id, amount FROM result;
SELECT CASE WHEN ((SELECT list(column_name) FROM (DESCRIBE result)) = (SELECT list(column_name) FROM (DESCRIBE orders)) AND (SELECT amount FROM result WHERE order_id = 1) = 144 AND (SELECT sum(amount) FROM result) = 2280.89 AND (SELECT count(amount) FROM result) = 9) THEN 'as expected' ELSE error('amount was not replaced in place by amount times 1.2 with no column added') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “amount was not replaced in place by amount times 1.2 with no column added”.
  • 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 (2)
Apache NiFi (1)
  • UpdateRecordread 2026-09-27

    Update the record fields selected by RecordPath with user-defined values

EasyMorph (1)
  • Modify column(s)read 2026-09-27

    Overwrite existing columns with values computed by expressions

OpenRefine (1)
  • Transform…read 2026-09-27

    Rewrite every cell of a column with a GREL, Jython or Clojure expression, with a preview

Pentaho Data Integration (2)
Polars (1)
Power Query M (1)
Trifacta (2)
  • Edit with formularead 2026-09-27

    Set the values of one or more columns to the result of a formula

  • Set Transformread 2026-09-27

    Overwrite a column with a literal or expression, optionally only on rows meeting a test

VisiData (3)
  • setcell-exprread 2026-09-23

    Set current cell to the result of a Python expression on its row

  • setcol-exprread 2026-09-23

    Set current column for selected rows to the result of a Python expression

  • setcol-iterread 2026-09-23

    Set current column for selected rows from items of a Python sequence expression

Background

In DuckDB, SELECT * REPLACE (lower(city) AS city) returns every column of the table, with city replaced by lower(city).[1] Polars' DataFrame.with_columns replaces an existing column when an added column has the same name.[2]

When to use it

Overwrite when the old values have no further use, such as a code that only needs trimming or a unit that only needs converting, and later steps expect the column's existing name. (Meridian's view) Keep the original, by copying it first or by leaving the source table untouched, whenever the formula might be wrong, since an overwritten column no longer shows what it held. (Meridian's view)

dplyr's mutate() returns a modified existing column in its original location, and the number of rows is not affected.[3]

The method

DuckDB's REPLACE clause replaces specific columns of a star expression with alternative expressions, several at once, as in REPLACE (col1 / 1_000 AS col1, col2 / 1_000 AS col2).[4]

Sources

  1. [1]SELECT Clause – DuckDBdocumentation
  2. [2]polars.DataFrame.with_columns — Polars documentationdocumentation
  3. [3]Create, modify, and delete columns — mutate • dplyrdocumentation
  4. [4]Star Expression – DuckDBdocumentation