All Products
Search

This Product

    MaxCompute:TRUNCATE

    Document Center

    MaxCompute:TRUNCATE

    Last Updated:Feb 02, 2026

    TRUNCATE removes all data from a table or partitions without deleting the table structure. This operation is faster than DELETE because it does not scan rows individually.

    Syntax

    Truncate a non-partitioned table

    TRUNCATE TABLE <table_name>;

    Truncate partition data

    Without filter condition:

    TRUNCATE TABLE <table_name> PARTITION <partition_spec>[, PARTITION <partition_spec>...];

    With filter condition:

    TRUNCATE TABLE <table_name> PARTITION <partition_filter_condition>;

    Parameters

    Parameter

    Required

    Description

    table_name

    Yes

    The name of the table to truncate. For partitioned tables, this is the name of the partitioned table.

    partition_spec

    Yes (for partitioned tables without filter)

    The partition specification. Format: (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)

    - partition_col: The partition column name (case-insensitive)
    - partition_col_value: The partition value (case-sensitive)


    Specify multiple partitions by separating partition specifications with commas.

    partition_filter_condition

    Yes (when using filter)

    The partition filter condition (case-insensitive). Supports the following formats:

    - partition (<partition_col> <relational_operator> <partition_col_value>)
    - partition (scalar(<partition_col>) <relational_operator> <partition_col_value>)
    - partition (<partition_filter_condition1> AND|OR <partition_filter_condition2>)
    - partition (NOT <partition_filter_condition>)
    - partition (<partition_filter_condition1>)[, partition (<partition_filter_condition2>), ...]


    - partition_col: The partition column name
    - relational_operator: A relational operator (such as =, !=, >, <, LIKE, etc.)
    - partition_col_value: The comparison value or regular expression that matches the partition column data type
    - scalar(): A scalar function that processes the partition column value before comparison

    Logical operators: Supports NOT, AND, and OR
    - Use NOT to get the complement of a filter condition
    - Use AND or OR to combine multiple filter conditions
    - When multiple partition filter clauses are separated by commas, they are combined with OR logic

    Examples

    Example 1: Truncate a non-partitioned table

    -- Remove all data from the sales table
TRUNCATE TABLE sales;

    Example 2: Truncate a single partition

    -- Remove sales records for December 2013 in Hangzhou region
TRUNCATE TABLE sale_detail PARTITION (sale_date='201312', region='hangzhou');

    Example 3: Truncate multiple partitions

    -- Remove sales records for December 2013 in both Hangzhou and Shanghai regions
TRUNCATE TABLE sale_detail 
  PARTITION (sale_date='201312', region='hangzhou'), 
  PARTITION (sale_date='201312', region='shanghai');

    Example 4: Truncate partitions using filter condition

    -- Remove all partitions in Hangzhou region where sale_date starts with '2013'
TRUNCATE TABLE sale_detail PARTITION (sale_date LIKE '2013%' AND region='hangzhou');

    Example 5: Truncate partitions using scalar function

    -- Remove partitions using a scalar function to process the partition column
TRUNCATE TABLE sale_detail PARTITION (scalar(sale_date) > '201300');

    Example 6: Truncate partitions using NOT operator

    -- Remove all partitions except those in Hangzhou region
TRUNCATE TABLE sale_detail PARTITION (NOT region='hangzhou');

    Example 7: Truncate partitions using OR logic

    -- Remove partitions matching multiple conditions (OR logic)
TRUNCATE TABLE sale_detail 
  PARTITION (region='hangzhou'), 
  PARTITION (region='shanghai');

    Related commands

    • CREATE TABLE: Create non-partitioned tables, partitioned tables, external tables, or clustered tables

    • ALTER TABLE: Modify table operations

    • DROP TABLE: Delete partitioned or non-partitioned tables

    • DESC TABLE/VIEW: View information about MaxCompute internal tables, views, materialized views, external tables, clustered tables, or transactional tables

    • SHOW: View SQL DDL statements, list all tables and views in a project, or list all partitions in a table