如何做如果不存在于 SQLite

我正在尝试将这一行从 MSSQLServer 移植到 SQLite

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

看起来 SQLite 不支持如果不存在，或者至少我不能让它工作。 我是不是漏掉了什么简单的东西? 有没有变通的方法？

154771 次浏览
小开
最佳答案

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');
小开

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

小开

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.

小开

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


提交答案