SQLite - INSERT IF NOT EXISTS ELSE UPDATE ??

Question

Hi. I've found a few "would be" solutions for the classic "How do I insert a new record or update one if it already exists" but I cannot get any of them to work in SQLite.

I have a table defined as follows:

CREATE TABLE Book 
ID     INTEGER PRIMARY KEY AUTOINCREMENT,
Name   VARCHAR(60) UNIQUE,
TypeID INTEGER,
Level  INTEGER,
Seen   INTEGER

What I want to do is add a record with a unique Name. If the Name already exists, I want to modify the fields.

Can somebody tell me how to do this please?

Answer 1

Firstly update it. If affected row count = 0 then insert it. Its the easiest and suitable for all RDBMS.

Answer 2

Have a look at http://sqlite.org/lang_conflict.html.

You want something like:

insert or replace into Book (Name, TypeID, Level, Seen) values ( ... )

Answer 3

I figured it out:

INSERT OR REPLACE INTO Book (Name,TypeID,Level,Seen) VALUES( 'Superman', '2', '15', '0' ) 

Thanks for all your suggestions.

Answer 4

Instead of using INSERT OR REPLACE INTO... you can just use REPLACE INTO...

When there are no property is set to UNIQUE... then it keeps on adding duplicate data instead of replacing.

What do i do in such a scenario. I get email_id as unique but in database that field is not set as UNIQUE... ???