TRUNCATE TABLE
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_name [PARTITION partition_spec];Parameters
table_name- The name of an existing table.
PARTITION ( partition_spec :[ partition_column = partition_col_value, partition_column = partition_col_value, ...] )- Specifies one or more partition column and value pairs. The partition value is optional.
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 partion 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