ALTER TABLE ... UPDATE Statements

ALTER TABLE [db.]table [ON CLUSTER cluster] UPDATE column1 = expr1 [, ...] [IN PARTITION partition_id] WHERE filter_expr

Manipulates data matching the specified filtering expression. Implemented as a mutation.

The ALTER TABLE prefix makes this syntax different from most other systems supporting SQL. It is intended to signify that unlike similar queries in OLTP databases this is a heavy operation not designed for frequent use.

The filter_expr must be of type UInt8. This query updates values of specified columns to the values of corresponding expressions in rows for which the filter_expr takes a non-zero value. Values are cast to the column type using the CAST operator. Updating columns that are used in the calculation of the primary or the partition key is not supported. One query can contain several commands separated by commas. The synchronicity of the query processing is defined by the mutations_sync setting. By default, it is asynchronous. See also

Mutations
Synchronicity of ALTER Queries
mutations_sync setting
Lightweight UPDATE - Alternative lightweight update using patch parts
APPLY PATCHES - Manually apply patches from lightweight updates

Materialized columns

A MATERIALIZED column whose expression reads an updated column is recalculated by the mutation, so its stored value stays consistent with the new data.

Columns calculated from EPHEMERAL columns

An EPHEMERAL column exists only for the duration of an INSERT and is never stored, so a MATERIALIZED column calculated from one cannot be recalculated by a mutation. Such a column keeps the value computed at INSERT time, which then no longer matches its expression:

CREATE TABLE test
(
    x Int32,
    e Int32 EPHEMERAL 0,
    m Int32 MATERIALIZED x + e
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO test (x, e) VALUES (1, 7);

ALTER TABLE test UPDATE x = 2 WHERE 1;

SELECT x, m FROM test;

┌─x─┬─m─┐
│ 2 │ 8 │
└───┴───┘

m is 8, the value calculated during INSERT, and not 2 + 7: the value of e is not available outside the INSERT that supplied it. The mutation writes a warning to the server log when it skips a column for this reason. To bring such a column up to date, re-INSERT the affected rows.

Blog: Handling Updates and Deletes in ClickHouse

SQL Reference

Data Types

Engines

Functions

Formats

Interfaces

Settings

System Tables

Data Lakes

Materialized columns

Columns calculated from EPHEMERAL columns

​Materialized columns

​Columns calculated from EPHEMERAL columns

​Related content

Materialized columns

Columns calculated from EPHEMERAL columns

Related content