How can I drop a table in a database if another table exists?

Question

I'm trying to drop a table in a database if another table exists.

I made a backup of a table using this query:

ALTER TABLE AppProfile RENAME TO AppProfile_backup

Now I'd like to restore the backup table and delete AppProfile via something like:

DROP TABLE AppProfile IF EXISTS TABLE AppProfile_backup
(ALTER TABLE AppProfile_backup RENAME TO AppProfile) IF NOT EXISTS AppProfile

However, I get syntax errors for both lines, near the "IF" token.

For background about this problem, I'm using the SQLiteOpenHelper class in Android. You can execute raw SQL in that environment by making the call [db_handler_instance].getWritableDatabase().execSQL("random query");

Answer 1

What about doing this in Java? You can query the data dictionary ...

String sql = "SELECT 1 "
           + "  FROM sqlite_master  " 
           + " WHERE type = 'table' "
           + "   AND name = 'AppProfile_backup'";

Cursor cursor = db.rawQuery(sql, null);

... and drop the table, if the backup table is existing:

if (cursor.moveToNext())
    db.execSQL("DROP TABLE AppProfile;");
else
    db.execSQL("ALTER TABLE ...");

Answer 2

You need to check for the table existence by looking for it in the definition of your database something like the following should do the trick

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))