TRUNCATE TABLE

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.

If the table is cached, the command clears cached data of the table and all its dependents that refer to it. The cache will be lazily filled when the table or the dependents are accessed the next time.

Syntax

TRUNCATE TABLE table_identifier [ partition_spec ]

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|
+----+------+---+
+----+------+---+