SQLite Truncate Table

In SQLite, there is no TRUNCATE TABLE command, but you can use SQLite'sDELETEDELETE command to delete all data from an existing table.

Syntax

The basic syntax of the DELETE command is as follows:

sqlite> DELETE FROM table_name;

However, this method cannot reset the auto-increment value to zero.

If you want to reset the increment value to zero, you can use the following method:

sqlite> DELETE FROM sqlite_sequence WHERE name = 'table_name';

When a SQLite database contains an auto-increment column, a table named sqlite_sequence is automatically created. This table contains two columns: name and seq. name records the table where the auto-increment column is located, and seq records the current sequence number (the number of the next record is the current sequence number plus 1). If you want to reset the sequence number of a particular auto-increment column to zero, you just need to modify the sqlite_sequence table.

UPDATE sqlite_sequence SET seq = 0 WHERE name = 'table_name';

Examples

Suppose the COMPANY table has the following records:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Below is an example of deleting records from the above table:

SQLite> DELETE FROM sqlite_sequence WHERE name = 'COMPANY';
SQLite> VACUUM;

Now, the records in the COMPANY table have been completely deleted, and using the SELECT statement will produce no output.