REPLACE TABLE

Overview

The REPLACE statement allows you to update a table atomically.

This statement is supported for the Atomic and Replicated database engines, which are the default database engines for ClickHouse and ClickHouse Cloud respectively.

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:

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:

REPLACE TABLE myOldTable
ENGINE = MergeTree()
ORDER BY CounterID 
AS
SELECT * FROM myOldTable
WHERE CounterID <12345;

Syntax

{CREATE [OR REPLACE] | REPLACE} TABLE [db.]table_name

All syntax forms for the CREATE statement also work for this statement. Invoking REPLACE for a non-existent table will cause an error.

Examples

Local
Cloud

Consider the following table:

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:

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:

REPLACE TABLE base.t1 (n UInt64) 
ENGINE = MergeTree 
ORDER BY n;

INSERT INTO base.t1 VALUES (3);

SELECT * FROM base.t1;

┌─n─┐
│ 3 │
└───┘

Consider the following table on ClickHouse Cloud:

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:

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:

REPLACE TABLE base.t1 (n UInt64) 
ENGINE = MergeTree 
ORDER BY n;

INSERT INTO base.t1 VALUES (3);

SELECT * FROM base.t1;

3

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Overview

Syntax

Examples

​Overview

​Syntax

​Examples

Overview

Syntax

Examples