Checking duplicate while inserting in SQLite

Question

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.

Kingdom

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.

Feudalize

Yes, I didn't aware of that. Thanks :) – Kingdom

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.

Incitement

Thanks @Incitement , thats true. names are just example's , I am using label as a unique. – Kingdom

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.

Snake