Truncate 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 crop a table only if it exists?

Unfortunately, this causes an error:

 DELETE FROM someTable IF EXISTS 

This also does not work:

 DELETE IF EXISTS FROM someTable 

Thank.

Answer 1

IMHO, it is more efficient to discard the table and recreate it. And yes, you can use "IF EXISTS" in this case.

Answer 2

This is a two-step process:

• Delete all data from this table using:

   Delete from TableName 

• Then:

   DELETE FROM SQLITE_SEQUENCE WHERE name='TableName'; 

Answer 3

Just do delete . This is from the SQLite documentation:

Optimization Truncate

"When WHERE is not specified in the DELETE statement, and the table to be deleted does not have triggers, SQLite uses optimization to erase the entire contents of the table without having to visit each row of the table separately. This truncation optimization does delete before version 3.6.5, truncate optimization also meant that the interfaces sqlite3_changes () and sqlite3_total_changes () and the pragma count_changes will not actually return the number of deleted rows. This problem has been fixed since version 3.6.5 ".

Answer 4

I got it to work with:

 SQLiteDatabase db= this.getWritableDatabase(); db.delete(TABLE_NAME, null, null); 

Answer 5

 SELECT name FROM sqlite_master where name = '<TABLE_NAME_HERE>' 

If the table name does not exist, then there will be no returned records!

You can also use

 SELECT count(name) FROM sqlite_master where name = '<TABLE_NAME_HERE>' 

if the counter is 1, then there is a table, otherwise it will return 0

Answer 6

After uninstallation, I also use the VACUUM command. So for the full TRUNCATE equivalent, I use this code:

 DELETE FROM <table>; UPDATE SQLITE_SEQUENCE SET seq = 0 WHERE name = '<table>'; VACUUM; 

Removing for me does not work for reset Auto Increment

 DELETE FROM "sqlite_sequence" WHERE "name"='<table>'; 

To learn more about VACUUM, you can go here: https://blogs.gnome.org/jnelson/2015/01/06/sqlite-vacuum-and-auto_vacuum/

Answer 7

Unfortunately, we do not have the TRUNCATE TABLE command in SQLite, but you can use the SQLite DELETE command to delete the full data from the existing table, although it is recommended to use the DROP TABLE command to delete the full table and re-create it again.

Answer 8

"sqllite" does not have the order of "TRUNCATE", like mysql, then we should get a different path ... this function (reset_table) frist deletes all the data in the table, and then reset the AUTOINCREMENT key in the table .... now you can use this function every time 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(); }