TRUNCATE TABLE (SQL Analytics)
Removes all the rows from a table or partition(s). The table must not be a view
or an external or temporary table. In order to truncate multiple partitions at once, specify the partitions in partition_spec
. If no partition_spec
is specified, removes all partitions in the table.
Parameters
table_identifier
[database_name.] table_name
: A table name, optionally qualified with a database name.delta.`<path-to-table>`
: The location of an existing Delta table.
partition_spec
Optional comma-separated list of key-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|
+----+------+---+
-- Remove all rows from the table in the specified partition
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|
+----+------+---+
-- Remove all rows from the table from all partitions
TRUNCATE TABLE Student;
SELECT * FROM Student;
+----+------+---+
|name|rollno|age|
+----+------+---+
+----+------+---+