TRUNCATE

TRUNCATE — empties a table

Syntax

TRUNCATE TABLE [schema.]tableName

Description

TRUNCATE operates on the table instead of individual rows. Therefore the command TRUNCATE TABLE removes all rows from a table without logging individual row deletions. It is more efficient than using DELETE without a WHERE clause for large tables.

The user must have the DELETE privilege on the table in order to invoke TRUNCATE on it.

When using multiple statements in a single call, the TRUNCATE command can only be used if it is the first statement in the call. If it is not, an error is returned.

For example:

create table if not exists a (a int); truncate table a;

syntax error on line 1

create table if not exists a (a int); truncate table a;

                                                      ^
expected = got TABLE

Parameters

tableName: Name of the table to be truncated (optionally schema-qualified).

Example

CREATE TABLE tab_truncate (col1 STRING);
INSERT INTO tab_truncate VALUES ('row1'),('row2'),('row3');
SELECT * FROM tab_truncate;
 COL1
 -----
 row1
 row2
 row3

TRUNCATE TABLE tab_truncate;
SELECT * FROM tab_truncate;

/* There are no records selected */