Shape / rows · All operations

Edit cells by hand

Change individual cells by hand, insert or delete specific rows.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one row out per row in, with columns added or changed
Level
several clauses of SQL, or a window

The DuckDB step

Hand edits held as a patch 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 result AS
WITH patch(order_id, amount) AS (VALUES (4, 75.00))
SELECT o.* REPLACE (coalesce(p.amount, o.amount) AS amount)
FROM orders o LEFT JOIN patch p USING (order_id);
SELECT * FROM result;
SELECT CASE WHEN ((SELECT max(amount) FILTER (WHERE order_id = 4) = 75 AND sum(amount) FILTER (WHERE order_id <> 4) = 1900.74 FROM result)) THEN 'as expected' ELSE error('the patch did not change order 4 alone') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the patch did not change order 4 alone”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

  • Insert rows

    Add blank, constant or supplied rows at a chosen position.

Cut, copy and paste cells or rows

Move cell values or rows through a clipboard into other cells or positions.

Delete chosen rows

Remove specific rows, picked by hand or by a condition.

Change cell values by hand

Type or set a new value over chosen cells, keeping every row.

Other hand edits

Annotate highlighted rows, shift cells sideways or restructure outline rows by hand.

Replace whole rows

Swap matching rows, or a range of rows, for supplied replacement rows.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

DuckDB (2)
  • DELETEread 2026-09-27

    Remove rows matching a condition

  • UPDATEread 2026-09-27

    Change values in rows matching a condition

KNIME Analytics Platform (4)
OpenRefine (2)
  • Add blank rowsread 2026-09-27

    Insert blank rows: prepend one, append one, or add several

  • Edit one cell at a timeread 2026-09-27

    Edit a single cell's value and type by hand, optionally applying it to all identical cells

Polars (1)
Power Query M (3)
RapidMiner Studio (1)
  • Set Dataread 2026-09-27

    Set the value of one or more columns in one specified row

Tableau Prep (1)
  • Edit Valueread 2026-09-27

    Type a new value over one or more selected values of a field

VisiData (26)
  • add-rowread 2026-09-23

    Insert a blank row

  • add-rowsread 2026-09-23

    Insert N blank rows

  • autoedit-Nread 2026-09-23

    (experimental) Typing a digit starts editing current cell with that digit

  • combine-selectedread 2026-09-23

    (orgmode) Combine selected rows into a new org entry

  • copy-cellread 2026-09-23

    Copy current cell to internal clipboard

  • copy-cellsread 2026-09-23

    Copy current column values of selected rows to clipboard

  • copy-rowread 2026-09-23

    Copy current row to internal clipboard (Directory Sheet: copy the file)

  • copy-selectedread 2026-09-23

    Copy selected rows to internal clipboard

  • cut-cellread 2026-09-23

    Null current cell and move its value to clipboard

  • cut-cellsread 2026-09-23

    Null current column for selected rows and move values to clipboard

  • cut-rowread 2026-09-23

    Delete current row and move it to clipboard

  • cut-selectedread 2026-09-23

    Delete selected rows and move them to clipboard

  • delete-rowread 2026-09-23

    Delete current row

  • edit-cellread 2026-09-23

    Edit contents of current cell

  • paste-afterread 2026-09-23

    Paste clipboard rows after current row

  • paste-beforeread 2026-09-23

    Paste clipboard rows before current row

  • paste-cellread 2026-09-23

    Set current cell to the last clipboard value

  • paste-dataread 2026-09-23

    (orgmode) Move clipboard rows to children of current row

  • paste-sectionsread 2026-09-23

    (orgmode) Move clipboard rows to children of current row

  • setcol-clipboardread 2026-09-23

    Set current column for selected rows to the last clipboard value

  • setcol-inputread 2026-09-23

    Set current column for selected rows to one typed value

  • slide-cells-rightread 2026-09-23

    (experimental) Shift values of current row one column right; leftmost cell set to null

  • sysedit-cellread 2026-09-23

    Edit current cell in the external $EDITOR

  • sysedit-selectedread 2026-09-23

    Edit selected rows in the external $EDITOR

  • syspaste-cellsread 2026-09-23

    Paste system clipboard into cells starting at cursor

  • syspaste-cells-selectedread 2026-09-23

    Paste system clipboard into selected rows

Background

In OpenRefine, a single cell can be edited by hand in a pop-up window, which can also change its data type and apply the edit to all identical cells.[1] An SQL UPDATE statement changes the data of one or more records in a table, and an SQL DELETE statement removes one or more rows from a table.[2][3] In Tableau Prep, a value typed over with Edit Value is recorded in the Changes pane.[4]

When to use it

In OpenRefine, any activity that changes the data can be undone, and each project's change history is saved with the project's data.[5] OpenRefine can extract a project's operations as JSON and apply them to another project, but edits to a single cell cannot be replicated that way.[5] In Tableau Prep, the Changes pane lets a user edit or remove changes, drag them to change the order in which they are applied, and add a description for other users.[4] A hand edit is easy to lose when the source is refreshed, so keep each one written down as data, keyed on the record's identifier, with a note of why it was made. (Meridian's view) Key a correction on a stable identifier rather than on a row's position, since positions shift as soon as rows are added, removed or re-sorted. (Meridian's view)

In DuckDB, a DELETE with no WHERE clause deletes every record in the table.[6] An SQL UPDATE with no WHERE condition updates every row of the table.[2] Before running an UPDATE or DELETE meant for a handful of rows, run its WHERE clause as a SELECT and count what comes back. (Meridian's view) In DuckDB, a DELETE keeps the rows for which its WHERE clause is false or NULL.[6] A table of corrections should carry each key once; check for repeated keys before applying it. (Meridian's view)

The method

In DuckDB, UPDATE changes the values of the specified columns in all rows that satisfy its condition, and columns not named in the SET clause keep their previous values.[7] A DuckDB table can be updated from the values in another table, either by naming that table in a FROM clause or with a sub-select, which completes the update in bulk.[7] DuckDB's REPLACE clause on a star expression replaces specific columns with alternative expressions.[8]

Running DELETE in DuckDB does not by itself reclaim space: rows are in general only marked as deleted, and the space is reclaimed at a CHECKPOINT.[6] In Tableau Prep, any edit made to a value must be compatible with the field's data type.[4]

Compared with the alternatives

It is not possible to delete an individual value with an SQL DELETE; an UPDATE that sets the value to NULL is used instead.[3] OpenRefine offers a kind of find and replace by editing one cell and choosing to apply the edit to all identical cells.[1] DuckDB's MERGE INTO suits a change set that holds some keys and their changed values, and can also delete the rows a set of ids matches.[9]

Sources

  1. [1]Cell editing | OpenRefinedocumentation
  2. [2]Update (SQL) - Wikipediasecondary source
  3. [3]Delete (SQL) - Wikipediasecondary source
  4. [4]Clean and Shape Data in Tableau Prep - Tableaudocumentation
  5. [5]Running OpenRefine | OpenRefinedocumentation
  6. [6]DELETE Statement – DuckDBdocumentation
  7. [7]UPDATE Statement – DuckDBdocumentation
  8. [8]Star Expression – DuckDBdocumentation
  9. [9]MERGE INTO Statement – DuckDBdocumentation