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
