Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

INSERT IF NOT EXISTS ELSE UPDATE?

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?

like image 472
SparkyNZ Avatar asked Sep 03 '10 10:09

SparkyNZ


People also ask

Does MySQL update insert if not exists?

Often you have the situation that you need to check if an table entry exists, before you can make an update. If it does not exist, you have to do an insert first. Unfortunately, this the 'ON DUPLICATE KEY' statement only works on PRIMARY KEY and UNIQUE columns.

Does insert update?

If the row being inserted already exists, INSERT OR UPDATE performs an UPDATE operation, updating the row with the specified column values. An update occurs even when the specified data values are identical to the existing data.


1 Answers

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

You want something like:

insert or replace into Book (ID, Name, TypeID, Level, Seen) values ((select ID from Book where Name = "SearchName"), "SearchName", ...); 

Note that any field not in the insert list will be set to NULL if the row already exists in the table. This is why there's a subselect for the ID column: In the replacement case the statement would set it to NULL and then a fresh ID would be allocated.

This approach can also be used if you want to leave particular field values alone if the row in the replacement case but set the field to NULL in the insert case.

For example, assuming you want to leave Seen alone:

insert or replace into Book (ID, Name, TypeID, Level, Seen) values (    (select ID from Book where Name = "SearchName"),    "SearchName",     5,     6,     (select Seen from Book where Name = "SearchName")); 
like image 155
janm Avatar answered Oct 12 '22 00:10

janm