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

> Documentation for ALTER TABLE ... UPDATE Statements

# ALTER TABLE ... UPDATE

```sql theme={null}
ALTER TABLE [db.]table [ON CLUSTER cluster] UPDATE column1 = expr1 [, ...] [IN PARTITION partition_expr1 [, partition_expr2 ...]] WHERE filter_expr
```

Manipulates data matching the specified filtering expression. Implemented as a [mutation](/reference/statements/alter/index#mutations).

<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.
</Note>

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 `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](/reference/settings/session-settings/optimize) 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](/reference/settings/session-settings/mutations#mutations_sync) setting. By default, it is asynchronous.

**See also**

* [Mutations](/reference/statements/alter/index#mutations)
* [Synchronicity of ALTER Queries](/reference/statements/alter/index#synchronicity-of-alter-queries)
* [mutations\_sync](/reference/settings/session-settings/mutations#mutations_sync) setting
* [Lightweight `UPDATE`](/reference/statements/update) - Alternative lightweight update using patch parts
* [`APPLY PATCHES`](/reference/statements/alter/apply-patches) - Manually apply patches from lightweight updates

<h2 id="materialized-columns">
  Materialized columns
</h2>

A [`MATERIALIZED`](/reference/statements/create/table#materialized) column whose expression reads an
updated column is recalculated by the mutation, so its stored value stays consistent with the new data.

<h3 id="columns-calculated-from-ephemeral-columns">
  Columns calculated from EPHEMERAL columns
</h3>

An [`EPHEMERAL`](/reference/statements/create/table#ephemeral) column exists only for the duration of an
`INSERT` and is never stored, so a `MATERIALIZED` column calculated from one cannot be recalculated by a
mutation. Such a column keeps the value computed at `INSERT` time, which then no longer matches its
expression:

```sql theme={null}
CREATE TABLE test
(
    x Int32,
    e Int32 EPHEMERAL 0,
    m Int32 MATERIALIZED x + e
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO test (x, e) VALUES (1, 7);

ALTER TABLE test UPDATE x = 2 WHERE 1;

SELECT x, m FROM test;
```

```text theme={null}
┌─x─┬─m─┐
│ 2 │ 8 │
└───┴───┘
```

`m` is `8`, the value calculated during `INSERT`, and not `2 + 7`: the value of `e` is not available
outside the `INSERT` that supplied it. The mutation writes a warning to the server log when it skips a
column for this reason. To bring such a column up to date, re-`INSERT` the affected rows.

<h2 id="related-content">
  Related content
</h2>

* Blog: [Handling Updates and Deletes in ClickHouse](https://clickhouse.com/blog/handling-updates-and-deletes-in-clickhouse)