Truncate a SQLite table if it exists

Question

To truncate a table in SQLite I need to use this syntax   DELETE FROM someTable   But how do I truncate the table only if it exists   Unfortunately this throws an error   DELETE FROM someTable IF EXISTS   This doesn t work either   DELETE IF EXISTS FROM someTable   Thanks

User · Answer

It is the two step process    Delete all data from that table using   Delete from TableName  Then   DELETE FROM SQLITE SEQUENCE WHERE name  TableName

User · Answer

sqllite  dosn t have  TRUNCATE   order like than mysql  then we have to get other way    this function  reset table  frist delete all data in table and then reset AUTOINCREMENT key in table     now you can use this function every where you want     example    private SQLiteDatabase mydb  private final String dbPath    data data your project name databases    private final String dbName    your db name     public void reset table String table name       open db         mydb execSQL  Delete from   table name       mydb execSQL  DELETE FROM SQLITE SEQUENCE WHERE name    table name             close db       public void open db         mydb   SQLiteDatabase openDatabase dbPath   dbName     db   null  SQLiteDatabase OPEN READWRITE     public void close db         mydb close

User · Answer

Just do delete  This is from the SQLite documentation      The Truncate Optimization       When the WHERE is omitted from a DELETE statement and the table being deleted has no triggers  SQLite uses an optimization to erase the entire table content without having to visit each row of the table individually  This  truncate  optimization makes the delete run much faster  Prior to SQLite version 3 6 5  the truncate optimization also meant that the sqlite3 changes   and sqlite3 total changes   interfaces and the count changes pragma will not actually return the number of deleted rows  That problem has been fixed as of version 3 6 5

User · Answer

After deleting I m also using VACUUM command  So for full TRUNCATE equivalent I use this code   DELETE FROM  lt table gt   UPDATE SQLITE SEQUENCE SET seq   0 WHERE name     lt table gt    VACUUM    Deleting isn t working for me for reset Auto Increment    DELETE FROM  sqlite sequence  WHERE  name    lt table gt      To read more about VACUUM you can go here  https   blogs gnome org jnelson 2015 01 06 sqlite-vacuum-and-auto vacuum

User · Answer

SELECT name FROM sqlite master where name     lt TABLE NAME HERE gt     If the table name does not exist then there would not be any records returned   You can as well use   SELECT count name  FROM sqlite master where name     lt TABLE NAME HERE gt         if the count is 1  means table exists  otherwise  it would return 0

User · Answer

I got it to work with   SQLiteDatabase db  this getWritableDatabase            db delete TABLE NAME  null  null

User · Answer

It is Simple  just follow 2 steps  Step  1  Fire query  Delete from tableName   It will delete all records from table   Step  2  There is table named  sqlite sequence  in Sqlite Database  just browse it and you can set sequence table wise to  0  so it will start from auto id  1     See the screenshot attached

User · Answer

Unfortunately  we do not have a  TRUNCATE TABLE  command in SQLite  but you can use SQLite s DELETE command to delete the complete data from an existing table  though it is recommended to use the DROP TABLE command to drop the complete table and re-create it once again

User · Answer

IMHO  it is more efficient to drop the table and re-create it  And yes  you can use  IF EXISTS  in this case

[sqlite] Truncate a SQLite table if it exists?

Examples related to sqlite