> ## 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.

> Lightweight updates simplify the process of updating data in the database using patch parts.

# The Lightweight UPDATE Statement

export const galaxyOnClick = eventName => () => {
  try {
    if (typeof window !== "undefined" && window.galaxy && eventName) {
      window.galaxy.track(eventName, {
        interaction: "click"
      });
    }
  } catch (e) {}
};

export const BetaBadge = ({link, galaxyTrack, galaxyEvent}) => {
  if (link) {
    return <a href={link} target="_blank" rel="noopener noreferrer" className="betaBadge" onClick={galaxyTrack && galaxyEvent ? galaxyOnClick(galaxyEvent) : undefined}>
                <Icon />
                <span>Beta</span>
            </a>;
  }
  return <div className="betaBadge">
            <Icon />
            <span>
                Beta feature. 
                <u>
                    <a href="/docs/beta-and-experimental-features#beta-features">
                        Learn more.
                    </a>
                </u>
            </span>
        </div>;
};

<Note>
  Lightweight updates are currently beta.
  If you run into problems, kindly open an issue in the [ClickHouse repository](https://github.com/clickhouse/clickhouse/issues).
</Note>

The lightweight `UPDATE` statement updates rows in a table `[db.]table` that match the expression `filter_expr`.
It is called "lightweight update" to contrast it to the [`ALTER TABLE ... UPDATE`](/reference/statements/alter/update) query, which is a heavyweight process that rewrites entire columns in data parts.
It is only available for the [`MergeTree`](/reference/engines/table-engines/mergetree-family/mergetree) table engine family.

```sql theme={null}
UPDATE [db.]table [ON CLUSTER cluster] SET column1 = expr1 [, ...] [IN PARTITION partition_expr] WHERE filter_expr;
```

The `filter_expr` must be of type `UInt8`. This query updates values of the specified columns to the values of the 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 used in the calculation of the primary or partition keys is not supported.

<h2 id="examples">
  Examples
</h2>

```sql theme={null}
UPDATE hits SET Title = 'Updated Title' WHERE EventDate = today();

UPDATE wikistat SET hits = hits + 1, time = now() WHERE path = 'ClickHouse';
```

<h2 id="lightweight-update-does-not-update-data-immediately">
  Lightweight updates do not update data immediately
</h2>

Lightweight `UPDATE` is implemented using **patch parts** - a special kind of data part that contains only the updated columns and rows.
A lightweight `UPDATE` creates patch parts but does not immediately modify the original data physically in storage.
The process of updating is similar to a `INSERT ... SELECT ...` query but the `UPDATE` query waits until the patch part creation is completed before returning.

The updated values are:

* **Immediately visible** in `SELECT` queries through patches application
* **Physically materialized** only during subsequent merges and mutations
* **Automatically cleaned up** once all active parts have the patches materialized

<h2 id="lightweight-update-requirements">
  Lightweight updates requirements
</h2>

Lightweight updates are supported for [`MergeTree`](/reference/engines/table-engines/mergetree-family/mergetree), [`ReplacingMergeTree`](/reference/engines/table-engines/mergetree-family/replacingmergetree), [`CollapsingMergeTree`](/reference/engines/table-engines/mergetree-family/collapsingmergetree), [`VersionedCollapsingMergeTree`](/reference/engines/table-engines/mergetree-family/versionedcollapsingmergetree) engines and their [`Replicated`](/reference/engines/table-engines/mergetree-family/replication) and [`Shared`](/products/cloud/features/infrastructure/shared-merge-tree) versions.

To use lightweight updates, materialization of `_block_number` and `_block_offset` columns must be enabled using table settings [`enable_block_number_column`](/reference/settings/merge-tree-settings#enable_block_number_column) and [`enable_block_offset_column`](/reference/settings/merge-tree-settings#enable_block_offset_column).

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

A [lightweight `DELETE`](/snippets/delete) query can be run as a lightweight `UPDATE` instead of a `ALTER UPDATE` mutation. The implementation of lightweight `DELETE` is controlled by setting [`lightweight_delete_mode`](/reference/settings/session-settings#lightweight_delete_mode).

<h2 id="performance-considerations">
  Performance considerations
</h2>

**Advantages of lightweight updates:**

* The latency of the update is comparable to the latency of the `INSERT ... SELECT ...` query
* Only updated columns and values are written, not entire columns in data parts
* No need to wait for currently running merges/mutations to complete, therefore the latency of an update is predictable
* Parallel execution of lightweight updates is possible

**Potential performance impacts:**

* Adds an overhead to `SELECT` queries that need to apply patches
* [Skipping indexes](/reference/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-data_skipping-indexes) will not be used for columns in data parts that have patches to be applied. [Projections](/reference/engines/table-engines/mergetree-family/mergetree#projections) will not be used if there are patch parts for table, including for data parts that don't have patches to be applied.
* Small updates which are too frequent may lead to a "too many parts" error. It is recommended to batch several updates into a single query, for example by putting ids for updates in a single `IN` clause in the `WHERE` clause
* Lightweight updates are designed to update small amounts of rows (up to about 10% of the table). If you need to update a larger amount, it is recommended to use the [`ALTER TABLE ... UPDATE`](/reference/statements/alter/update) mutation

<h2 id="concurrent-operations">
  Concurrent operations
</h2>

Lightweight updates don't wait for currently running merges/mutations to complete unlike heavy mutations.
The consistency of concurrent lightweight updates is controlled by settings [`update_sequential_consistency`](/reference/settings/session-settings#update_sequential_consistency) and [`update_parallel_mode`](/reference/settings/session-settings#update_parallel_mode).

<h2 id="update-permissions">
  Update permissions
</h2>

`UPDATE` requires the `ALTER UPDATE` privilege. To enable `UPDATE` statements on a specific table for a given user, run:

```sql theme={null}
GRANT ALTER UPDATE ON db.table TO username;
```

<h2 id="details-of-the-implementation">
  Details of the implementation
</h2>

Patch parts are the same as the regular parts, but contain only updated columns and several system columns:

* `_part` - the name of the original part
* `_part_offset` - the row number in the original part
* `_block_number` - the block number of the row in the original part
* `_block_offset` - the block offset of the row in the original part
* `_data_version` - the data version of the updated data (block number allocated for the `UPDATE` query)

On average it gives about 40 bytes (uncompressed data) of overhead per updated row in the patch parts.
System columns help to find rows in the original part which should be updated.
System columns are related to the [virtual columns](/reference/engines/table-engines/mergetree-family/mergetree#virtual-columns) in the original part, which are added for reading if patch parts should be applied.
Patch parts are sorted by `_part` and `_part_offset`.

Patch parts belong to different partitions than the original part.
The partition id of the patch part is `patch-<hash of column names in patch part>-<original_partition_id>`.
Therefore patch parts with different columns are stored in different partitions.
For example three updates `SET x = 1 WHERE <cond>`, `SET y = 1 WHERE <cond>` and `SET x = 1, y = 1 WHERE <cond>` will create three patch parts in three different partitions.

Patch parts can be merged among themselves to reduce the amount of applied patches on `SELECT` queries and reduce the overhead. Merging of patch parts uses the [replacing](/reference/engines/table-engines/mergetree-family/replacingmergetree) merge algorithm with `_data_version` as a version column.
Therefore patch parts always store the latest version for each updated row in the part.

Lightweight updates don't wait for currently running merges and mutations to finish and always use a current snapshot of data parts to execute an update and produce a patch part.
Because of that there can be two cases of applying patch parts.

For example if we read part `A`, we need to apply patch part `X`:

* if `X` contains part `A` itself. It happens if `A` was not participating in merge when `UPDATE` was executed.
* if `X` contains part `B` and `C`, which are covered by part `A`. It happens if there was a merge (`B`, `C`) -> `A` running when `UPDATE` was executed.

For these two cases there are two ways to apply patch parts respectively:

* Using merge by sorted columns `_part`, `_part_offset`.
* Using join by `_block_number`, `_block_offset` columns.

The join mode is slower and requires more memory than the merge mode, but it is used less often.

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

* [`ALTER UPDATE`](/reference/statements/alter/update) - Heavy `UPDATE` operations
* [Lightweight `DELETE`](/snippets/delete) - Lightweight `DELETE` operations
* [`APPLY PATCHES`](/reference/statements/alter/apply-patches) - Force physical materialization of patches to data parts (mutation operation)