> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-trino-dialect.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_id] 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 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)