How to do IF NOT EXISTS in SQLite

sql sqlite
AngryHacker picture AngryHacker · Feb 10, 2009 · Viewed 139.9k times · Source

I am trying to port this line from MS SQL Server to SQLite

IF NOT EXISTS(SELECT 1 FROM EVENTTYPE WHERE EventTypeName = 'ANI Received') 
    INSERT INTO EVENTTYPE (EventTypeName) VALUES ('ANI Received');

It seems that SQLite does not support IF NOT EXISTS or at least I can't make it work. Am I missing something simple? Is there a workaround?

Answer

beach picture beach · Feb 10, 2009

How about this?

INSERT OR IGNORE INTO EVENTTYPE (EventTypeName) VALUES 'ANI Received'

(Untested as I don't have SQLite... however this link is quite descriptive.)

Additionally, this should also work:

INSERT INTO EVENTTYPE (EventTypeName)
SELECT 'ANI Received'
WHERE NOT EXISTS (SELECT 1 FROM EVENTTYPE WHERE EventTypeName = 'ANI Received');

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How to list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?

Read More
Is it possible to insert multiple rows at a time in an SQLite database?

In MySQL you can insert multiple rows like this: INSERT INTO 'tablename' ('column1', 'column2') VALUES ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'); However, I am getting an error when …

Read More