How to do IF NOT EXISTS in SQLite
SqlSqliteSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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');
Solution 2 - Sql
If you want to ignore the insertion of existing value, there must be a Key field in your Table. Just create a table With Primary Key Field Like:
CREATE TABLE IF NOT EXISTS TblUsers (UserId INTEGER PRIMARY KEY, UserName varchar(100), ContactName varchar(100),Password varchar(100));
And Then Insert Or Replace / Insert Or Ignore Query on the Table Like:
INSERT OR REPLACE INTO TblUsers (UserId, UserName, ContactName ,Password) VALUES('1','UserName','ContactName','Password');
It Will Not Let it Re-Enter The Existing Primary key Value... This Is how you can Check Whether a Value exists in the table or not.
Solution 3 - Sql
You can also set a Constraint on a Table with the KEY fields and set On Conflict "Ignore"
> When an applicable constraint violation occurs, the IGNORE resolution > algorithm skips the one row that contains the constraint violation and > continues processing subsequent rows of the SQL statement as if > nothing went wrong. Other rows before and after the row that contained > the constraint violation are inserted or updated normally. No error is > returned when the IGNORE conflict resolution algorithm is used.
Solution 4 - Sql
you can do "NOT EXISTS" with math, inverting EXISTS output:
SELECT (1-EXISTS(<Your_SELECT>));
Your_SELECT is: SELECT 1 FROM EVENTTYPE WHERE EventTypeName = 'ANI Received'
If exists is 1 , 1-1 result is 0. If exists is 0 , 1-0 result is 1