SQLite Query to Insert a record If not exists

tags:

query
sqlite

views:

129

answers:

+3 Q:

SQLite Query to Insert a record If not exists

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.

+5 A:

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.

Konerak 2010-05-06 09:13:54

sqlite> INSERT INTO users VALUES(1,'test',0) ON CONFLICT IGNORE;SQL error: near "ON": syntax errorsqlite> sqlite> INSERT OR IGNORE users VALUES(1,'test',0);SQL error: near "users": syntax errorsqlite> sqlite> INSERT INTO users VALUES(1,'test',0) OR IGNORE;SQL error: near "OR": syntax error Could you just provide me an example. just couldn't successfully use it. I want to check the name already in or then add this new record if 'test' is not in table

Tharindu Madushanka 2010-05-06 09:51:41

This has solved the issue. Adding UNIQUE to name field. Now I get SQL error and data not added.CREATE TABLE users('pk' INTEGER PRIMARY KEY, 'name' VARCHAR(100) UNIQUE, 'count' INTEGER);

Tharindu Madushanka 2010-05-06 10:13:38

INSERT OR IGNORE into users VALUES ...

Konerak 2010-05-06 10:51:08

Why not simply add an unique index on name?

munissor 2010-05-06 09:14:08

CREATE TABLE users('pk' INTEGER PRIMARY KEY, 'name' VARCHAR(100), 'count' INTEGER);This is my current table. How could I change it. Sorry I just could understand :(

Tharindu Madushanka 2010-05-06 09:55:30

Just got it.CREATE TABLE users('pk' INTEGER PRIMARY KEY, 'name' VARCHAR(100) UNIQUE, 'count' INTEGER);

Tharindu Madushanka 2010-05-06 10:12:43

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)

Derk 2010-05-07 03:16:22

ansaurus

tags:

views:

answers:

SQLite Query to Insert a record If not exists

related questions