Checking duplicate while inserting in SQLite

Question

Hello All,

I am trying to insert a data into SQLite database using Python.

INSERT INTO DATA_TABLE(UID,LABEL) VALUES (NULL, "UK")  
    WHERE "UK" NOT EXISTS IN (SELECT LABEL FROM DATA_TABLE);

This query is dynamically generated from Python and I am checking whether the date is already exist in the table before inserting and its not working in SQLite database. Getting this near "WHERE": syntax error error.

Am I doing something wrong ?

Thanks for your help.

Answer 1

I'm pretty sure that INSERT doesn't have a WHERE clause (the documentation doesn't mention any). What you can do:

• create a unique index on LABEL
• use INSERT OR FAIL
• if that triggers an error, the row already exists.

Answer 2

It is giving you a syntax error because it is not allowed syntax. From your example I presume the schema is probably:

create table data_table (uid integer primary key autoincrement.
     label string);

in which case primary key implies unique. But, since you allow auto-generation of uid then you don't care what it's value is, you just don't want duplicate labels in which case you actually care that label be unique so tell it so:

create table data_table (uid integer primary key autoincrement,
     label string unique on conflict fail);

which then works as expected:

sqlite> insert into data_table (label) values ("uk");
sqlite> insert into data_table (label) values ("uk");
Error: column label is not unique
sqlite> select * from data_table;
1|uk

Incidentally, if the names data_table, uid, and label aren't example names for the purposes of this question then you should use more meaningful names as these are horribly uninformative.

Answer 3

INSERT INTO DATA_TABLE(UID,LABEL) VALUES (NULL, "UK")  
WHERE NOT EXISTS(SELECT 1 FROM DATA_TABLE WHERE LABEL="UK");

you can use this instead of INSERT OR FAIL.