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

  • table

    The 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
ErrorWhat happenedHow to recover
HTQL-3005 The table is protected and cannot be truncatedThe table is a platform or product table, or its name matches no registered tableChoose a table that permits truncation, and check the name
HTQL-3002 Unauthorized accessThe caller lacks the Delete permission on the table, which is the permission TRUNCATE needsAsk a role that holds it
HTQL-6001 TRUNCATE: table not foundThe table is registered but is no longer in the databaseCheck the name
HTQL-6001 Refused: …The protected routine refused the name or the schemaUse 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.