title_wip

Before downloading, the system checks if the partition exists and the table structure matches. The most appropriate replica is selected automatically from the healthy replicas.

Although the query is called `ALTER TABLE`, it does not change the table structure and does not immediately change the data available in the table.

## MOVE PARTITION|PART[​](#move-partitionpart "Direct link to MOVE PARTITION|PART")

Moves partitions or data parts to another volume or disk for `MergeTree`-engine tables. See [Using Multiple Block Devices for Data Storage](/docs/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-multiple-volumes).

```
ALTER TABLE table_name [ON CLUSTER cluster] MOVE PARTITION|PART partition_expr TO DISK|VOLUME 'disk_name'
```

The `ALTER TABLE t MOVE` query:

- Not replicated, because different replicas can have different storage policies.
- Returns an error if the specified disk or volume is not configured. Query also returns an error if conditions of data moving, that specified in the storage policy, can't be applied.
- Can return an error in the case, when data to be moved is already moved by a background process, concurrent `ALTER TABLE t MOVE` query or as a result of background data merging. A user shouldn't perform any additional actions in this case.

Example:

```
ALTER TABLE hits MOVE PART '20190301_14343_16206_438' TO VOLUME 'slow'
ALTER TABLE hits MOVE PARTITION '2019-09-01' TO DISK 'fast_ssd'
```

## UPDATE IN PARTITION[​](#update-in-partition "Direct link to UPDATE IN PARTITION")

Manipulates data in the specifies partition matching the specified filtering expression. Implemented as a [mutation](/docs/sql-reference/statements/alter#mutations).

Syntax:

```
ALTER TABLE [db.]table [ON CLUSTER cluster] UPDATE column1 = expr1 [, ...] [IN PARTITION partition_expr] WHERE filter_expr
```

### Example[​](#example "Direct link to Example")

```
-- using partition name
ALTER TABLE mt UPDATE x = x + 1 IN PARTITION 2 WHERE p = 2;

-- using partition id
ALTER TABLE mt UPDATE x = x + 1 IN PARTITION ID '2' WHERE p = 2;
```

### See Also[​](#see-also "Direct link to See Also")

- [UPDATE](/docs/sql-reference/statements/alter/partition#update-in-partition)

## DELETE IN PARTITION[​](#delete-in-partition "Direct link to DELETE IN PARTITION")

Deletes data in the specifies partition matching the specified filtering expression. Implemented as a [mutation](/docs/sql-reference/statements/alter#mutations).

Syntax:

```
ALTER TABLE [db.]table [ON CLUSTER cluster] DELETE [IN PARTITION partition_expr] WHERE filter_expr
```

### Example[​](#example-1 "Direct link to Example")

```
-- using partition name
ALTER TABLE mt DELETE IN PARTITION 2 WHERE p = 2;

-- using partition id
ALTER TABLE mt DELETE IN PARTITION ID '2' WHERE p = 2;
```

## REWRITE PARTS[​](#rewrite-parts "Direct link to REWRITE PARTS")

This will rewrite the parts from scratch, using all new settings. This makes sense because table-level settings like `use_const_adaptive_granularity` are applied only for newly written parts by default.

### Example[​](#example-rewrite-parts "Direct link to Example")

```
ALTER TABLE mt REWRITE PARTS;
ALTER TABLE mt REWRITE PARTS IN PARTITION 2;
```

### See Also[​](#see-also-1 "Direct link to See Also")

- [DELETE](/docs/sql-reference/statements/alter/delete)

## How to Set Partition Expression[​](#how-to-set-partition-expression "Direct link to How to Set Partition Expression")

You can specify the partition expression in `ALTER ... PARTITION` queries in different ways:

- As a value from the `partition` column of the `system.parts` table. For example, `ALTER TABLE visits DETACH PARTITION 201901`.
- Using the keyword `ALL`. It can be used only with DROP/DETACH/ATTACH/ATTACH FROM. For example, `ALTER TABLE visits ATTACH PARTITION ALL`.
- As a tuple of expressions or constants that matches (in types) the table partitioning keys tuple. In the case of a single element partitioning key, the expression should be wrapped in the `tuple (...)` function. For example, `ALTER TABLE visits DETACH PARTITION tuple(toYYYYMM(toDate('2019-01-25')))`.
- Using the partition ID. Partition ID is a string identifier of the partition (human-readable, if possible) that is used as the names of partitions in the file system and in ZooKeeper. The partition ID must be specified in the `PARTITION ID` clause, in a single quotes. For example, `ALTER TABLE visits DETACH PARTITION ID '201901'`.
- In the [ALTER ATTACH PART](#attach-partitionpart) and [DROP DETACHED PART](#drop-detached-partitionpart) query, to specify the name of a part, use string literal with a value from the `name` column of the [system.detached\_parts](/docs/operations/system-tables/detached_parts) table. For example, `ALTER TABLE visits ATTACH PART '201901_1_1_0'`.

Usage of quotes when specifying the partition depends on the type of partition expression. For example, for the `String` type, you have to specify its name in quotes (`'`). For the `Date` and `Int*` types no quotes are needed.

All the rules above are also true for the [OPTIMIZE](/docs/sql-reference/statements/optimize) query. If you need to specify the only partition when optimizing a non-partitioned table, set the expression `PARTITION tuple()`. For example:

```
OPTIMIZE TABLE table_not_partitioned PARTITION tuple() FINAL;
```

`IN PARTITION` specifies the partition to which the [UPDATE](/docs/sql-reference/statements/alter/update) or [DELETE](/docs/sql-reference/statements/alter/delete) expressions are applied as a result of the `ALTER TABLE` query. New parts are created only from the specified partition. In this way, `IN PARTITION` helps to reduce the load when the table is divided into many partitions, and you only need to update the data point-by-point.

The examples of `ALTER ... PARTITION` queries are demonstrated in the tests [`00502_custom_partitioning_local`](https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/00502_custom_partitioning_local.sql) and [`00502_custom_partitioning_replicated_zookeeper`](https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/00502_custom_partitioning_replicated_zookeeper.sql).

- [DETACH PARTITION|PART](#detach-partitionpart)- [DROP PARTITION|PART](#drop-partitionpart)- [DROP DETACHED PARTITION|PART](#drop-detached-partitionpart)- [FORGET PARTITION](#forget-partition)- [ATTACH PARTITION|PART](#attach-partitionpart)- [ATTACH PARTITION FROM](#attach-partition-from)- [REPLACE PARTITION](#replace-partition)- [MOVE PARTITION TO TABLE](#move-partition-to-table)- [CLEAR COLUMN IN PARTITION](#clear-column-in-partition)- [FREEZE PARTITION](#freeze-partition)- [UNFREEZE PARTITION](#unfreeze-partition)- [CLEAR INDEX IN PARTITION](#clear-index-in-partition)- [FETCH PARTITION|PART](#fetch-partitionpart)- [MOVE PARTITION|PART](#move-partitionpart)- [UPDATE IN PARTITION](#update-in-partition)
                              - [Example](#example)- [See Also](#see-also)- [DELETE IN PARTITION](#delete-in-partition)
                                - [Example](#example-1)- [REWRITE PARTS](#rewrite-parts)
                                  - [Example](#example-rewrite-parts)- [See Also](#see-also-1)- [How to Set Partition Expression](#how-to-set-partition-expression)

Was this page helpful?