Publish / database · All operations

Update rows in a table

A variant of Write to a database

Change the values of existing rows in a database table, matched by key or condition.

Status

In arcform

Not built yet

arcform does not do this yet. Its place on the roadmap

What other tools call it

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

Apache DataFusion (1)
  • UPDATEread 2026-09-27

    Change column values of rows matching a condition; in-memory tables support it

EasyMorph (1)
  • Update database tableread 2026-09-27

    Update rows of a database table with values from the EasyMorph table (paid editions only)

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

    Update database rows identified by key columns with values from the input table

Background

A Structured Query Language (SQL) UPDATE statement changes the data of one or more rows in a table: all of them, or those a WHERE condition picks.[1] In PostgreSQL, only the columns to be modified need be named in an UPDATE's SET clause; the other columns keep their previous values.[2] In PostgreSQL, it is not an error for an UPDATE to match no rows.[3]

When to use it

In DuckDB, when a target row matches several rows in an UPDATE's FROM clause, it is updated once, with the values of one of them, and which one is not specified.[4] Deduplicate the source on its key before updating from it, so that every target row has one answer. (Meridian's view)

In EasyMorph's Update database table action, each combination of key values must be unique and correspond to only one row, or the action fails.[5] Without matching key fields, EasyMorph's Update database table action runs an UPDATE with no WHERE clause, and fails if the dataset has more than one row.[5] Count the rows an update changed and compare the count with what was expected. (Meridian's view)

The method

In PostgreSQL, the new value in an UPDATE's SET clause can be any scalar expression, including one that uses the row's existing values.[3] In PostgreSQL, a table can be updated from information in other tables either with sub-selects or by naming the other tables in a FROM clause.[2] In DuckDB, updating from another table through a FROM clause or a sub-select completes the UPDATE in bulk for increased performance.[4] In DuckDB, an UPDATE's RETURNING clause returns the updated rows as they are after the update.[4]

The Halloween Problem is a database fault in which an update changes the physical location of a row, potentially letting the same update visit it again, and in some cases loop without end.[6] Don Chamberlin, Pat Selinger and Morton Astrahan discovered the Halloween Problem in the mid-1970s, on Halloween, while working on query optimisation.[6] Certain UPDATE statements can become an infinite loop when the WHERE clause and one or more SET clauses use an intertwined index.[1]

Compared with the alternatives

A SQL MERGE statement, also called upsert, inserts new records or updates or deletes existing ones depending on whether a condition matches.[7] MERGE was officially introduced in the SQL:2003 standard.[7] In DuckDB, MERGE INTO throws an error if a target row matches more than one source row.[4] In DuckDB, an INSERT with ON CONFLICT DO UPDATE turns into an UPDATE of the conflicting row when it conflicts with a UNIQUE or PRIMARY KEY constraint.[8]

Sources

  1. [1]Update (SQL) - Wikipediasecondary source
  2. [2]PostgreSQL: Documentation: 18: UPDATEdocumentation
  3. [3]PostgreSQL: Documentation: 18: 6.2. Updating Datadocumentation
  4. [4]UPDATE Statement – DuckDBdocumentation
  5. [5]UPDATE DATABASE TABLE [EasyMorph Help]documentation
  6. [6]Halloween Problem - Wikipediasecondary source
  7. [7]Merge (SQL) - Wikipediasecondary source
  8. [8]INSERT Statement – DuckDBdocumentation