SQLite delete from only if the table exists

Question

Right now I have this statement

DELETE FROM sqlite_sequence where name = 'table_name';

which works fine in most cases, but now I have a case where the sqlite_sequence table is not being created and so I get back

no such table: sqlite_sequence

I need a statement that only does the delete if the table exists. I've found this statement to tell me if the table exists or not

SELECT name FROM sqlite_master WHERE type='table';

But so far I've been unable to successfully pair it with the delete satement

Maybe this will be helpful to you. http://stackoverflow.com/questions/9803679/delete-row-if-table-exists-sql — Matt Clark, Dec 05 '12 at 17:30
great link, but unfortunately I'm not seeing any solutions that work with SQLite — lightswitch05, Dec 05 '12 at 17:33

score 7 · Accepted Answer · answered Dec 05 '12 at 17:52

7

The table existence check should include the name, of course

SELECT count(*) FROM sqlite_master WHERE type='table' AND name='table_name';

But you'll have to do it from the calling application in separate statements, i.e. run the above, and based on the count (naturally 1=true, 0=false) as boolean value, you can proceed to run the DELETE statement.

answered Dec 05 '12 at 17:52

RichardTheKiwi

105,798
26
196
262

2

Although I was looking for a way to do it in a single statement, I'm making this the solution since there doesn't seem to be a way in SQLite to do this in a single statment – lightswitch05 Dec 05 '12 at 19:41
You can just use `SELECT 1 ...` instead of `COUNT(*)` – Dmitry Zaytsev Nov 13 '13 at 11:31

SQLite delete from only if the table exists

1 Answers1