SQLite Query to Insert a record If not exists

Question

I want to insert a record into a sqlite table if its actually not inserted.

Let's say it has three fields pk, name, address

I want to INSERT new record with name if that name not added preveously.

Can we do with this in a single Query. Seems like its slightly different from SQL Queries sometimes.

Answer 1

Yes, you can do that with a single query.

INSERT ON CONFLICT IGNORE should help you: http://www.sqlite.org/lang_conflict.html

Put a unique key on the name, this will create a conflict when you try inserting a record if the name already exists.

The default is ABORT, so without the IGNORE, the statement will return an error. If you don't want that, use IGNORE.

Answer 2

If you can't make use of a UNIQUE INDEX in combination with INSERT INTO or INSERT OR IGNORE INTO, you could write a query like this;

INSERT INTO table (column) SELECT value WHERE NOT EXISTS (SELECT 1                    FROM table                    WHERE column = value)