How to do IF NOT EXISTS in SQLite

Question

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

User · Accepted Answer

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

User · Answer

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    SQLite Documentation

User · Answer

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

[sql] How to do IF NOT EXISTS in SQLite

Examples related to sql

Examples related to sqlite