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

Question

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.

score 129 · Answer 1 · edited Nov 29 '11 at 07:30

129

You can use:

DROP TABLE IF EXISTS TABLE_NAME;

edited Nov 29 '11 at 07:30

animuson

53,861
28
137
147

answered Nov 29 '11 at 07:28

Amit Jatrana

1,475
2
9
2

6

The OP literally cannot do this and said as much in the question. – Ian McLaird Oct 15 '20 at 20:40
@IanMcLaird Although true interestingly this is first question in SO and this answer is what I was looking for (along with 120 other people apparently) – WinEunuuchs2Unix Feb 27 '21 at 14:52
@IanMcLaird It could still help other people, like myself. – user3286381 Mar 07 '21 at 05:48

Donal Fellows · Accepted Answer · 2010-09-09T09:08:50.490

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.

score 8 · Answer 3 · edited Feb 01 '13 at 16:17

8

Just use this.

DROP TABLE TABLE_NAME;

edited Feb 01 '13 at 16:17

Ben

51,770
36
127
149

answered Feb 01 '13 at 15:59

Ahmad affandi

81
1
1

This works but it gives and Error if the table already exists – patJnr Mar 18 '19 at 07:16
1

@patJnr: You mean, it gives an error if the table does **not** exist. True, but as `DROP TABLE IF EXISTS` is not supported as for the OP, this is the best bet. Just catch the error, analyze it and proceed. – tanius Apr 26 '20 at 20:26

score 3 · Answer 4 · answered Sep 09 '10 at 08:50

3

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"

answered Sep 09 '10 at 08:50

Marcus

1,866
1
20
33

Hmm... how would I use that when dropping the table? – HyderA Sep 09 '10 at 08:52
can't you write "if (SELECT count(*) FROM sqlite_master where tbl_name = "" and type="table") > 0 then drop table " Don't know if that's correct syntax for sqlite though. – Marcus Sep 09 '10 at 09:25

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

4 Answers4

Linked