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. 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 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
Related content
Last modified on September 2, 2026
