CODEWITHSUNDEEP
sqldatabasesqlite
HyderA
HyderA

Reputation: 21371

Drop table in old version of SQLite where IF EXISTS is not supported

My version of SQLite does not support the IF EXISTS operator. How can I drop a table which may or may not exist without getting an error slapped at me?

I can't update the version on a live application right now, so I cannot use a SQLite version that supports IF EXISTS.

Upvotes: 52

Views: 106565

Answers (4)

Ahmad affandi
Ahmad affandi

Reputation: 81

Just use this.

DROP TABLE TABLE_NAME;

Upvotes: 8

Amit Jatrana
Amit Jatrana

Reputation: 1495

You can use:

DROP TABLE IF EXISTS TABLE_NAME;

Upvotes: 131

Donal Fellows
Donal Fellows

Reputation: 137567

The official documentation says to use IF EXISTS, so I suspect your best plan is to upgrade.

If you can't, you need to see whether you can do some trivial operation on the table that will succeed whether or not the table is empty; if it succeeds you should delete the table, if it fails the table is already gone. An example of the sort of operation to try might be:

SELECT COUNT(*) FROM theTable;

Note that you need to trap the possible error from this at the language level, and you might want to wrap the whole lot (probe, error-trap, drop table) in a transaction. Of course, the other approach if you're getting into error handling is to just drop the table and handle the error anyway.

Upvotes: 34

Marcus
Marcus

Reputation: 1876

You could somehow use the metadata table in your query to find out if the table exist:

SELECT count(*) > 0 FROM sqlite_master where tbl_name = "<table_name>" and type="table"

Upvotes: 3

Related Questions