Truncate a SQLite table if it exists?

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.

It is the two step process:

1. Delete all data from that table using:

   Delete from TableName
   

2. Then:

   DELETE FROM SQLITE_SEQUENCE WHERE name='TableName';
   

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

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."

I got it to work with:

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

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

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 = '<TABLE_NAME_HERE>' 

if the count is 1, means table exists, otherwise, it would return 0

After deleting I'm also using VACUUM command. So for full TRUNCATE equivalent I use this code:

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

Deleting isn't working for me for reset Auto Increment

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

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

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.

"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();
}

**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.

there is no truncate command in sqlite.

but can be achieved by:

1. delete all records;
   Delete from 'Table1';

then

2. set auto-increment to 0;
   UPDATE SQLITE_SEQUENCE SET seq = 0 WHERE name = 'Table1';