insert row if one with equal value does not already exist-CodePudding

I'm trying to do an insert into table if the table doesn't already contain a field with a specified value. For this I am using this command: INSERT INTO zyprexa (NULL,'hello',0,0,0) SELECT date WHERE NOT EXISTS (SELECT 1 FROM zyprexa WHERE date='hello'); so it should insert a row with the values null,'hello',0,0,0. And then if it is executed again it should not do anything since there is already a row with the date value "hello". But it gives me a syntax error and doesn't run it even the first time. All the best.

CodePudding user response：

The correct syntax is:

INSERT INTO zyprexa (col1, col2, col3, col4, col5) 
SELECT NULL, 'hello', 0, 0, 0 
WHERE NOT EXISTS (SELECT 1 FROM zyprexa WHERE date = 'hello');

where col1, col2, col3, col4, col5 are the names of the columns that will receive the values (for example col2 should be date).

If you want the column date to be unique in your table, you should define it as such:

CREATE TABLE zyprexa  (
......
date TEXT UNIQUE,
......
);

and then you can execute INSERT statements with INSERT OR IGNORE, in which case if you try to insert a new row with a date (or any other column defined as unique) that already exists in the table, the insertion will fail (without any error):

INSERT OR IGNORE INTO zyprexa (col1, col2, col3, col4, col5) 
VALUES (NULL, 'hello', 0, 0, 0);