0001 ---
0002 layout: global
0003 title: TRUNCATE TABLE
0004 displayTitle: TRUNCATE TABLE
0005 license: |
0006 Licensed to the Apache Software Foundation (ASF) under one or more
0007 contributor license agreements. See the NOTICE file distributed with
0008 this work for additional information regarding copyright ownership.
0009 The ASF licenses this file to You under the Apache License, Version 2.0
0010 (the "License"); you may not use this file except in compliance with
0011 the License. You may obtain a copy of the License at
0012
0013 http://www.apache.org/licenses/LICENSE-2.0
0014
0015 Unless required by applicable law or agreed to in writing, software
0016 distributed under the License is distributed on an "AS IS" BASIS,
0017 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0018 See the License for the specific language governing permissions and
0019 limitations under the License.
0020 ---
0021
0022 ### Description
0023
0024 The `TRUNCATE TABLE` statement removes all the rows from a table or partition(s). The table must not be a view
0025 or an external/temporary table. In order to truncate multiple partitions at once, the user can specify the partitions
0026 in `partition_spec`. If no `partition_spec` is specified it will remove all partitions in the table.
0027
0028 ### Syntax
0029
0030 ```sql
0031 TRUNCATE TABLE table_identifier [ partition_spec ]
0032 ```
0033
0034 ### Parameters
0035
0036 * **table_identifier**
0037
0038 Specifies a table name, which may be optionally qualified with a database name.
0039
0040 **Syntax:** `[ database_name. ] table_name`
0041
0042 * **partition_spec**
0043
0044 An optional parameter that specifies a comma separated list of key and value pairs
0045 for partitions.
0046
0047 **Syntax:** `PARTITION ( partition_col_name = partition_col_val [ , ... ] )`
0048
0049 ### Examples
0050
0051 ```sql
0052 -- Create table Student with partition
0053 CREATE TABLE Student (name STRING, rollno INT) PARTITIONED BY (age INT);
0054
0055 SELECT * FROM Student;
0056 +----+------+---+
0057 |name|rollno|age|
0058 +----+------+---+
0059 | ABC| 1| 10|
0060 | DEF| 2| 10|
0061 | XYZ| 3| 12|
0062 +----+------+---+
0063
0064 -- Removes all rows from the table in the partition specified
0065 TRUNCATE TABLE Student partition(age=10);
0066
0067 -- After truncate execution, records belonging to partition age=10 are removed
0068 SELECT * FROM Student;
0069 +----+------+---+
0070 |name|rollno|age|
0071 +----+------+---+
0072 | XYZ| 3| 12|
0073 +----+------+---+
0074
0075 -- Removes all rows from the table from all partitions
0076 TRUNCATE TABLE Student;
0077
0078 SELECT * FROM Student;
0079 +----+------+---+
0080 |name|rollno|age|
0081 +----+------+---+
0082 +----+------+---+
0083 ```
0084
0085 ### Related Statements
0086
0087 * [DROP TABLE](sql-ref-syntax-ddl-drop-table.html)
0088 * [ALTER TABLE](sql-ref-syntax-ddl-alter-table.html)
