TRUNCATE
Empty a table in one statement and keep the table.
Syntax
Write TRUNCATE and the table's name. A name may be written plainly or in quotes.
TRUNCATE table
TRUNCATE "table"
A second name after a comma, TRUNCATE table_a, table_b, empties both tables.
Parameters and arguments
tableThe table to empty, as a name or a quoted name. The name is read as lower case. It must be registered as one that permits truncation
Required: Yes
Name one table, or two separated by a comma.
Examples
Remove every record from a working table.
Return type and behaviour
TRUNCATE answers that it succeeded and returns no rows. Every record is removed and the table stays.
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-3005 The table is protected and cannot be truncated | The table is a platform or product table, or its name matches no registered table | Choose a table that permits truncation, and check the name |
HTQL-3002 Unauthorized access | The caller lacks the Delete permission on the table, which is the permission TRUNCATE needs | Ask a role that holds it |
HTQL-6001 TRUNCATE: table not found | The table is registered but is no longer in the database | Check the name |
HTQL-6001 Refused: … | The protected routine refused the name or the schema | Use a table your account may write |
Placement and context
TRUNCATE is a statement of its own, and it may also be a step in the body of a FOR loop, where it runs for each row the loop visits. Emptying a table once is enough, so write it outside a loop.
Limitations
- TRUNCATE removes every record. It cannot be undone.
- To remove chosen records, use REMOVE with a FILTER.
Permissions and security
TRUNCATE runs as the caller. The table must be registered as one that permits truncation, which is true of the tables your account created and its link collections, and not of platform tables. The caller also needs the Delete permission on the table.
Best practices
- Use REMOVE with a FILTER when you want to delete only some records.
- Check the table's name before you run it.
- Name a pair of tables that reset together in one TRUNCATE.