All quickstarts

Data WarehousingOSS

Although ClickHouse is geared toward high volume analytic workloads, it is possible in some situations to modify or delete existing data. These operations are labeled “mutations” and are executed using the ALTER TABLE command.

Use the ALTER TABLE...UPDATE command to update rows in a table:

<expression> is the new value for the column where the <filter_expr> is satisfied. The <expression> must be the same datatype as the column or be convertible to the same datatype using the CAST operator. The <filter_expr> should return a UInt8 (zero or non-zero) value for each row of the data. Multiple UPDATE <column> statements can be combined in a single ALTER TABLE command separated by commas. Examples:

1. A mutation like this allows updating replacing visitor_ids with new ones using a dictionary lookup:
2. Modifying multiple values in one command can be more efficient than multiple commands:
3. Mutations can be executed ON CLUSTER for sharded tables:

Deleting data

Use the ALTER TABLE command to delete rows:

The <filter_expr> should return a UInt8 value for each row of data. Examples

1. Delete any records where a column is in an array of values:
2. What does this query alter?

View the DELETE statement docs page for more details.

Lightweight deletes

Another option for deleting rows is to use the DELETE FROM command, which is referred to as a lightweight delete. The deleted rows are marked as deleted immediately and will be automatically filtered out of all subsequent queries, so you don’t have to wait for a merging of parts or use the FINAL keyword. Cleanup of data happens asynchronously in the background.

For example, the following query deletes all rows from the hits table where the Title column contains the text hello:

A few notes about lightweight deletes:

• This feature is only available for the MergeTree table engine family.
• Lightweight deletes are synchronous by default, waiting for all replicas to process the delete. The behavior is controlled by the lightweight_deletes_sync setting.

All quickstarts