TRUNCATE TABLE

Applies to: check marked yes Databricks SQL Databricks Runtime

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.

Note

Delta Lake does not support partition clauses for TRUNCATE.

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_name [ PARTITION clause ]

Parameters

table_name

The name of the table to truncate. The name must not include a temporal specification. If the table cannot be found Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.
PARTITION clause

Optional specification of a partition. Not supported for Delta Lake.

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