> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation for the REPLACE TABLE statement

# REPLACE TABLE

<h2 id="overview">
  Overview
</h2>

The `REPLACE` statement allows you to update a table [atomically](/docs/concepts/core-concepts/glossary#atomicity).

<Note>
  This statement is supported for the [`Atomic`](/docs/reference/engines/database-engines/atomic) and [`Replicated`](/docs/reference/engines/database-engines/replicated) database engines,
  which are the default database engines for ClickHouse and ClickHouse Cloud respectively.
</Note>

Ordinarily, if you need to delete some data from a table,
you can create a new table and fill it with a `SELECT` statement that does not retrieve unwanted data,
then drop the old table and rename the new one.
This approach is demonstrated in the example below:

```sql theme={null}
CREATE TABLE myNewTable AS myOldTable;

INSERT INTO myNewTable
SELECT * FROM myOldTable 
WHERE CounterID <12345;

DROP TABLE myOldTable;

RENAME TABLE myNewTable TO myOldTable;
```

Instead of the approach above, it is also possible to use `REPLACE` (given you are using the default database engines) to achieve the same result:

```sql theme={null}
REPLACE TABLE myOldTable
ENGINE = MergeTree()
ORDER BY CounterID 
AS
SELECT * FROM myOldTable
WHERE CounterID <12345;
```

<h2 id="syntax">
  Syntax
</h2>

```sql theme={null}
{CREATE [OR REPLACE] | REPLACE} TABLE [db.]table_name
```

<Note>
  All syntax forms for the [`CREATE`](/docs/reference/statements/create/table) statement also work for this statement. Invoking `REPLACE` for a non-existent table will cause an error.
</Note>

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

<Tabs>
  <Tab title="Local">
    Consider the following table:

    ```sql theme={null}
    CREATE DATABASE base 
    ENGINE = Atomic;

    CREATE OR REPLACE TABLE base.t1
    (
        n UInt64,
        s String
    )
    ENGINE = MergeTree
    ORDER BY n;

    INSERT INTO base.t1 VALUES (1, 'test');

    SELECT * FROM base.t1;

    ┌─n─┬─s────┐
    │ 1 │ test │
    └───┴──────┘
    ```

    We can use the `REPLACE` statement to clear all the data:

    ```sql theme={null}
    CREATE OR REPLACE TABLE base.t1 
    (
        n UInt64,
        s Nullable(String)
    )
    ENGINE = MergeTree
    ORDER BY n;

    INSERT INTO base.t1 VALUES (2, null);

    SELECT * FROM base.t1;

    ┌─n─┬─s──┐
    │ 2 │ \N │
    └───┴────┘
    ```

    Or we can use the `REPLACE` statement to change the table structure:

    ```sql theme={null}
    REPLACE TABLE base.t1 (n UInt64) 
    ENGINE = MergeTree 
    ORDER BY n;

    INSERT INTO base.t1 VALUES (3);

    SELECT * FROM base.t1;

    ┌─n─┐
    │ 3 │
    └───┘
    ```
  </Tab>

  <Tab title="Cloud">
    Consider the following table on ClickHouse Cloud:

    ```sql theme={null}
    CREATE DATABASE base;

    CREATE OR REPLACE TABLE base.t1 
    (
        n UInt64,
        s String
    )
    ENGINE = MergeTree
    ORDER BY n;

    INSERT INTO base.t1 VALUES (1, 'test');

    SELECT * FROM base.t1;

    1    test
    ```

    We can use the `REPLACE` statement to clear all the data:

    ```sql theme={null}
    CREATE OR REPLACE TABLE base.t1 
    (
        n UInt64, 
        s Nullable(String)
    )
    ENGINE = MergeTree
    ORDER BY n;

    INSERT INTO base.t1 VALUES (2, null);

    SELECT * FROM base.t1;

    2    
    ```

    Or we can use the `REPLACE` statement to change the table structure:

    ```sql theme={null}
    REPLACE TABLE base.t1 (n UInt64) 
    ENGINE = MergeTree 
    ORDER BY n;

    INSERT INTO base.t1 VALUES (3);

    SELECT * FROM base.t1;

    3
    ```
  </Tab>
</Tabs>