Harsh Sharma Harsh Sharma - 5 months ago 10
SQL Question

How to truncate a database using sqllite in single command (Clear records from all the tables in database)

I am searching for bulk delete from all the tables from database.

DELETE FROM TABLE1,TABLE2,TABLE3; --(Is it possible)


like we have bulk insert

INSERT INTO TABLE (a,b,c) VALUES (1,2,3),(4,5,6),(7,8,9);


My code by which I am not satisfied as it is like this. It is able to do this.

Delete from table1;
Delete from table2;
Delete from table3;





public void truncateDatabase() {
ArrayList<String> tables = new ArrayList<>();
Cursor cursor = Database.getInstance().getWritableDatabase().rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
if (cursor.moveToFirst()) {
while (!cursor.isAfterLast()) {
tables.add(cursor.getString(0));
cursor.moveToNext();
}
}
cursor.close();

for (String table : tables) {
Database.getInstance().getWritableDatabase().execSQL("DELETE FROM " + table);
Database.getInstance().getWritableDatabase().execSQL("VACUUM");
}
}

CL. CL.
Answer

As shown in the documentation, a single DELETE statement works on a single table.

There's nothing wrong with executing multiple DELETE statements.

However, you should execute the VACUUM only once, and consider using a transaction:

SQLiteDatabase db = Database.getInstance().getWritableDatabase();
db.beginTransaction();
try {
    for (String table : tables) {
        db.execSQL("DELETE FROM " + table);
    }
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}
db.execSQL("VACUUM");
Comments