Skip to main content
Skip to main content
Edit this page

Update mutations

Update mutations refers to ALTER queries that manipulate table data through updates. Most notably they are queries like ALTER TABLE UPDATE, etc. Performing such queries will produce new mutated versions of the data partsA physical file (or directory) on disk that stores a portion of the table's data. This is different from a partition, which is a logical division of a table's data that is created using a partition key.. This means that such statements would trigger a rewrite of whole data partsA physical file (or directory) on disk that stores a portion of the table's data. This is different from a partition, which is a logical division of a table's data that is created using a partition key. for all data that was inserted before the mutationA mutation in ClickHouse refers to an operation that modifies or deletes existing data in a table, typically using commands like ALTER TABLE ... UPDATE or ALTER TABLE ... DELETE. Mutations are implemented as asynchronous background processes that rewrite entire data parts affected by the change, rather than modifying rows in place., translating to a large amount of write requests.

Info

For updates, you can avoid these large amounts of write requests by using specialised table engines like ReplacingMergeTree or CollapsingMergeTree instead of the default MergeTreeA MergeTree in ClickHouse is a table engine designed for high data ingest rates and large data volumes. It is the core storage engine in ClickHouse, providing features such as columnar storage, custom partitioning, sparse primary indexes, and support for background data merges. table engineTable engines in ClickHouse determine how data is written, stored and accessed. MergeTree is the most common table engine, and allows quick insertion of large amounts of data which get processed in the background..

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.

Note

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

Related content​