I would like to use something like this in SQlite
insert into table (column) values ('')if not exist;
I would like to use something like this in SQlite
insert into table (column) values ('')if not exist;
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 .
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 .