On 2017/12/21 1:56 PM, Patrick Skelton wrote:
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'///....
Two ways to achieve this. INSERT a Query[1] (in stead of Values) or
Ignore conflicts with existing Keys[2]
Example [1]:
*INSERT INTO NetworkLocks*
*(*
* PK_id,*
* owner_username,*
* unique_identifier,*
* creation_time*
*)*
*SELECT*
* @ID,*
* @owner_username,*
* @unique_identifier,*
* @creation_time*
*WHERE NOT EXISTS*
* ( SELECT * FROM NetworkLocks nl WHERE nl.PK_id = @ID );*
Example [2]:
*INSERT OR IGNORE 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 );*
HTH,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
