Try surrounding the VALUES bit with a SELECT insert into networklocks (...) select * from (values(?,?,?,?)) where not exists...;
As to the other suggestions: The only potential problem with "insert or ignore into" is that it will ignore any constraint violation for that record insert, be it the primary key, a unique constraint, a check constraint etc. So if you want it to ignore only primary key violations, but still yell on check constraint violations then that doesn't work. Another possible option is to include the ignore in the column definition itself: create table n1 (pk_Id int primary key on conflict ignore, foo text check (length(foo) > 0)); This will ignore a primary key issue, but still report a check constraint if you try to insert an empty string for foo. The potential issue with this is that <any> insert will silently ignore primary key errors, not just from your one specific query. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter Sent: Thursday, December 21, 2017 7:17 AM To: 'SQLite mailing list' Subject: Re: [sqlite] [EXTERNAL] How do I insert a record in an SQLite table only if the row does not already exist? INSERT OR IGNORE ... -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Patrick Skelton Gesendet: Donnerstag, 21. Dezember 2017 12:56 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] How do I insert a record in an SQLite table only if the row does not already exist? Hi, I am wanting to create an 'atomic' SQL script that will insert a record into a table only if the supplied record's primary key does not already exist, thus avoiding the constraint exception that occurs if the insert goes ahead. I have the following script which is wrong. I get an error saying the syntax is wrong near the 'WHERE'. *BEGIN EXCLUSIVE TRANSACTION;* *INSERT INTO NetworkLocks* *(* * PK_id,* * owner_username,* * unique_identifier,* * creation_time* *)* *VALUES* *(* * @ID,* * @owner_username,* * @unique_identifier,* * @creation_time* *)* *WHERE NOT EXISTS* * ( SELECT * FROM NetworkLocks nl WHERE nl.PK_id = @ID );* *END TRANSACTION;* Currently, my code works by relying on the uniqueness of the primary key (PK_id) and catch any exception. This is, however, a foreseeable error. It happens in normal operation. I'm not fond of exceptions for this situation. I was hoping to change the SQL script so that it always runs to completion but somehow returns a result. Perhaps the number of rows affected might be zero instead of one? Any help or advice would be very much appreciated. Also, as a newbie to SQLite, I am finding it difficult to get a handle on how to put together more complex queries. There are plenty of example on the internet of simple scripts, but it is difficult to know how to move beyond the basics. Any pointers to good sources of learning for this would be great. Kind wishes ~ Patrick _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
