How do I insert a record in an SQLite table only if the row does not already exist?

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

How do I insert a record in an SQLite table only if the row does not already exist?

Patrick Skelton
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] How do I insert a record in an SQLite table only if the row does not already exist?

Hick Gunter
INSERT OR IGNORE ...

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Patrick Skelton
Gesendet: Donnerstag, 21. Dezember 2017 12:56
An: [hidden email]
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How do I insert a record in an SQLite table only if the row does not already exist?

R Smith-2
In reply to this post by Patrick Skelton

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] How do I insert a record in an SQLite table only if the row does not already exist?

David Raymond
In reply to this post by Hick Gunter
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:[hidden email]] 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:[hidden email]] Im Auftrag von Patrick Skelton
Gesendet: Donnerstag, 21. Dezember 2017 12:56
An: [hidden email]
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

INSERT OR IGNORE ignores constraints. Bug ?

Simon Slavin-3


On 21 Dec 2017, at 3:46pm, David Raymond <[hidden email]> wrote:

> The only potential problem with "insert or ignore into" is that it will ignore any constraint violation for that record insert

Wait.  What ?

SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10));
sqlite> INSERT INTO MyTable VALUES (15);
sqlite> INSERT INTO MyTable VALUES (5);
Error: CHECK constraint failed: noless
sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
sqlite> SELECT * FROM MyTable;
15
sqlite>

What the hell ?  Why does that work ?  Isn’t it a huge bug ?  How did you discover it ?

My understanding is that using INSERT OR IGNORE meant that bad inserts would fail, but they would do so silently, without triggering an error result.

<https://sqlite.org/lang_conflict.html>

"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."

If I understand correctly, "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" means that a row that violates constraints will not be inserted.

I thought I could enforce policy by setting constraints.  Apparently not.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT OR IGNORE ignores constraints. Bug ?

Igor Tandetnik-2
On 12/21/2017 12:50 PM, Simon Slavin wrote:

> SQLite version 3.19.3 2017-06-27 16:48:08
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10));
> sqlite> INSERT INTO MyTable VALUES (15);
> sqlite> INSERT INTO MyTable VALUES (5);
> Error: CHECK constraint failed: noless
> sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
> sqlite> SELECT * FROM MyTable;
> 15
> sqlite>
>
> What the hell ?  Why does that work ?  Isn’t it a huge bug ?  How did you discover it ?
>
> My understanding is that using INSERT OR IGNORE meant that bad inserts would fail, but they would do so silently, without triggering an error result.

Isn't that precisely what happened in your example? Inserting 6 failed silently. What again seems to be the problem?

> If I understand correctly, "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" means that a row that violates constraints will not be inserted.

And in your example, it was not.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT OR IGNORE ignores constraints. Bug ?

Scott Robison-2
In reply to this post by Simon Slavin-3
On Dec 21, 2017 10:50 AM, "Simon Slavin" <[hidden email]> wrote:



On 21 Dec 2017, at 3:46pm, David Raymond <[hidden email]> wrote:

> The only potential problem with "insert or ignore into" is that it will
ignore any constraint violation for that record insert

Wait.  What ?

SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10));
sqlite> INSERT INTO MyTable VALUES (15);
sqlite> INSERT INTO MyTable VALUES (5);
Error: CHECK constraint failed: noless
sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
sqlite> SELECT * FROM MyTable;
15
sqlite>

What the hell ?  Why does that work ?  Isn’t it a huge bug ?  How did you
discover it ?

My understanding is that using INSERT OR IGNORE meant that bad inserts
would fail, but they would do so silently, without triggering an error
result.


Insert 15 succeeded, 5 failed with error, 6 failed without error. Seems
exactly as advertised. What am I missing?

Note that or ignore is statement specific, not transaction specific, if I
understand correctly.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT OR IGNORE ignores constraints. Bug ?

Simon Slavin-3
In reply to this post by Igor Tandetnik-2


On 21 Dec 2017, at 5:58pm, Igor Tandetnik <[hidden email]> wrote:

> Isn't that precisely what happened in your example? Inserting 6 failed silently. What again seems to be the problem?

I’m sorry.  You and Scott are quite right.  I have no idea what I was thinking.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] How do I insert a record in an SQLite table only if the row does not already exist?

Simon Slavin-3
In reply to this post by David Raymond


On 21 Dec 2017, at 3:46pm, David Raymond <[hidden email]> wrote:

> The only potential problem with "insert or ignore into" is that it will ignore any constraint violation for that record insert

Not true.

sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10));
sqlite> INSERT INTO MyTable VALUES (15);
sqlite> INSERT INTO MyTable VALUES (5);
Error: CHECK constraint failed: noless
sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
sqlite> SELECT * FROM MyTable;
15
sqlite>

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] How do I insert a record in an SQLite table only if the row does not already exist?

David Raymond
What I meant was it'll ignore the error message, not ignore the constraint.

So where you're inserting 5 there it'd be wrong to think "well, there was no error, so 5 is in the database from either before or now"

Say you have imported resource X for November, then you get December's version and want to import only the new stuff. If you do "insert or ignore" then yes, you won't waste time replacing the old stuff, but you also won't get the error notification that there was an issue with the new copy of the resource.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Simon Slavin
Sent: Thursday, December 21, 2017 1:15 PM
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?



On 21 Dec 2017, at 3:46pm, David Raymond <[hidden email]> wrote:

> The only potential problem with "insert or ignore into" is that it will ignore any constraint violation for that record insert

Not true.

sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10));
sqlite> INSERT INTO MyTable VALUES (15);
sqlite> INSERT INTO MyTable VALUES (5);
Error: CHECK constraint failed: noless
sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
sqlite> SELECT * FROM MyTable;
15
sqlite>

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT OR IGNORE ignores constraints. Bug ?

Keith Medcalf
In reply to this post by Simon Slavin-3

Simon,

Policy is being enforced.  You specifically declared in the table definition that rows must have (a > 10) in order to be "in the table".  The IGNORE as in INSERT OR IGNORE means exactly and precisely what it says:  INSERT the record if it is valid and IGNORE it oherwise.  You tried to insert a "bad" row and specified to ignore the error causing rows so that operation was ignored and the record was not inserted.

This is working as designed and as documented.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Simon Slavin
>Sent: Thursday, 21 December, 2017 10:50
>To: SQLite mailing list
>Subject: [sqlite] INSERT OR IGNORE ignores constraints. Bug ?
>
>
>
>On 21 Dec 2017, at 3:46pm, David Raymond <[hidden email]>
>wrote:
>
>> The only potential problem with "insert or ignore into" is that it
>will ignore any constraint violation for that record insert
>
>Wait.  What ?
>
>SQLite version 3.19.3 2017-06-27 16:48:08
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a >
>10));
>sqlite> INSERT INTO MyTable VALUES (15);
>sqlite> INSERT INTO MyTable VALUES (5);
>Error: CHECK constraint failed: noless
>sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
>sqlite> SELECT * FROM MyTable;
>15
>sqlite>
>
>What the hell ?  Why does that work ?  Isn’t it a huge bug ?  How did
>you discover it ?
>
>My understanding is that using INSERT OR IGNORE meant that bad
>inserts would fail, but they would do so silently, without triggering
>an error result.
>
><https://sqlite.org/lang_conflict.html>
>
>"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."
>
>If I understand correctly, "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" means that a row that violates constraints will not be
>inserted.
>
>I thought I could enforce policy by setting constraints.  Apparently
>not.
>
>Simon.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users