TRUNCATE TABLE

2020-01-21

Description

The TRUNCATE TABLE statement removes all the rows from a table or partition(s). The table must not be a view or an external/temporary table. In order to truncate multiple partitions at once, the user can specify the partitions in partition_spec. If no partition_spec is specified it will remove all partitions in the table.

Syntax

TRUNCATE TABLE table_identifier [ partition_spec ]

Parameters

table_identifier
Specifies a table name, which may be optionally qualified with a database name.

Syntax: [ database_name. ] table_name
partition_spec
An optional parameter that specifies a comma separated list of key and value pairs for partitions.

Syntax: PARTITION ( partition_col_name = partition_col_val [ , ... ] )

Examples

--Create table Student with partition CREATE TABLE Student ( name String, rollno INT) PARTITIONED BY (age int);

SELECT * from Student; +-------+---------+------+--+ | name | rollno | age | +-------+---------+------+--+ | ABC | 1 | 10 | | DEF | 2 | 10 | | XYZ | 3 | 12 | +-------+---------+------+--+

-- Removes all rows from the table in the partition specified TRUNCATE TABLE Student partition(age=10);

--After truncate execution, records belonging to partition age=10 are removed SELECT * from Student; +-------+---------+------+--+ | name | rollno | age | +-------+---------+------+--+ | XYZ | 3 | 12 | +-------+---------+------+--+

-- Removes all rows from the table from all partitions TRUNCATE TABLE Student;

SELECT * from Student; +-------+---------+------+--+ | name | rollno | age | +-------+---------+------+--+ +-------+---------+------+--+ No rows selected