ALTER TABLE ... DELETE

Documentation for ALTER TABLE … DELETE Statement

ALTER TABLE [db.]table [ON CLUSTER cluster] DELETE WHERE filter_expr

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

The ALTER TABLE prefix signals that this is a heavyweight operation, unlike similar queries in OLTP databases. ALTER TABLE ... DELETE rewrites every data part containing rows that match the filtering expression, generating substantial write I/O. Use it sparingly, as it can affect SELECT performance.

For MergeTree tables, consider using the DELETE FROM query, which performs a lightweight delete and can be considerably faster. For workloads that require frequent deletions or corrections, consider using ReplacingMergeTree or CollapsingMergeTree.

The filter_expr must be of type UInt8. The query deletes rows in the table for which this expression takes a non-zero value.

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

Related content

• Blog: Handling Updates and Deletes in ClickHouse