How to execute the INSERT only if the record does not exist?

4

I would like to use something like this in SQlite

insert into table (column) values ('')if not exist;
    
asked by anonymous 10.12.2017 / 13:35

2 answers

7

You can use INSERT OR IGNORE .

For this purpose, the column (s) concerned should be declared as UNIQUE .

When a constraint is violated, it is handled according to the conflict clause of constraint declared for the column, which by default is abort . If it is declared or not, it can be overridden in the INSERT command:

INSERT OR IGNORE INTO table (column) VALUES ('')

So the restriction violation is ignored.

See INSERT and ON CONFLICT clause .

    
10.12.2017 / 15:10
2

You can use unique to avoid repetition if this is the case. I found the example below on link

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE,
  CONSTRAINT name_unique UNIQUE (last_name, first_name)
);

In addition you can also use along with insert to perform validation. Using the above table, I could do something like this:

 insert into employees(last_name, first_name, hire_date)

 select 
    'Stuart','John','1990-01-01' 
 from 
    employees 
 where 
    not exists(select 1 from employees where first_name = 'john')

When the query has no results, it will not perform the insert and you can perform any filtering on where .

    
10.12.2017 / 14:14