> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Updating and deleting ClickHouse data

> Describes how to perform update and delete operations in ClickHouse

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.

<Tip>
  If you need to perform frequent updates, consider using [deduplication](/concepts/features/operations/insert/deduplication) in ClickHouse, which allows you to update
  and/or delete rows without generating a mutation event. Alternatively, use [lightweight updates](/reference/statements/update)
  or [lightweight deletes](/concepts/features/operations/delete/lightweight-delete)
</Tip>

<h2 id="updating-data">
  Updating data
</h2>

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

```sql theme={null}
ALTER TABLE [<database>.]<table> UPDATE <column> = <expression> WHERE <filter_expr>
```

`<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:

   ```sql theme={null}
   ALTER TABLE website.clicks
   UPDATE visitor_id = getDict('visitors', 'new_visitor_id', visitor_id)
   WHERE visit_date < '2022-01-01'
   ```

2. Modifying multiple values in one command can be more efficient than multiple commands:

   ```sql theme={null}
   ALTER TABLE website.clicks
   UPDATE url = substring(url, position(url, '://') + 3), visitor_id = new_visit_id
   WHERE visit_date < '2022-01-01'
   ```

3. Mutations can be executed `ON CLUSTER` for sharded tables:

   ```sql theme={null}
   ALTER TABLE clicks ON CLUSTER main_cluster
   UPDATE click_count = click_count / 2
   WHERE visitor_id ILIKE '%robot%'
   ```

<Note>
  It isn't possible to update columns that are part of the primary or sorting key.
</Note>

<h2 id="deleting-data">
  Deleting data
</h2>

Use the `ALTER TABLE` command to delete rows:

```sql theme={null}
ALTER TABLE [<database>.]<table> DELETE WHERE <filter_expr>
```

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:
   ```sql theme={null}
   ALTER TABLE website.clicks DELETE WHERE visitor_id in (253, 1002, 4277)
   ```

2. What does this query alter?
   ```sql theme={null}
   ALTER TABLE clicks ON CLUSTER main_cluster DELETE WHERE visit_date < '2022-01-02 15:00:00' AND page_id = '573'
   ```

<Note>
  To delete all of the data in a table, it is more efficient to use the command `TRUNCATE TABLE [<database].]<table>` command.  This command can also be executed `ON CLUSTER`.
</Note>

View the [`DELETE` statement](/snippets/delete) docs page for more details.

<h2 id="lightweight-deletes">
  Lightweight deletes
</h2>

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.

```sql theme={null}
DELETE FROM [db.]table [ON CLUSTER cluster] [WHERE expr]
```

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

```sql theme={null}
DELETE FROM hits WHERE Title LIKE '%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](/reference/settings/session-settings#lightweight_deletes_sync).