ALTER TABLE ... UPDATE

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

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

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 IN PARTITION clause limits the mutation to the listed partitions. Without it, on tables of the ReplicatedMergeTree family, when the optimize_mutations_with_partition_pruning setting is enabled (the default), ClickHouse automatically detects partition key conditions in filter_expr and only mutates the affected partitions. On non-replicated MergeTree tables, use an explicit IN PARTITION clause to limit the mutation to specific partitions.

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

Materialized columns

Columns calculated from EPHEMERAL columns

Related content