INSERT IF NOT EXISTS ELSE UPDATE?

Question

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

You need to set a constraint on the table to trigger a "conflict" which you then resolve by doing a replace:

CREATE TABLE data   (id INTEGER PRIMARY KEY, event_id INTEGER, track_id INTEGER, value REAL);
CREATE UNIQUE INDEX data_idx ON data(event_id, track_id);

Then you can issue:

INSERT OR REPLACE INTO data VALUES (NULL, 1, 2, 3);
INSERT OR REPLACE INTO data VALUES (NULL, 2, 2, 3);
INSERT OR REPLACE INTO data VALUES (NULL, 1, 2, 5);

The "SELECT * FROM data" will give you:

2|2|2|3.0
3|1|2|5.0

Note that the data.id is "3" and not "1" because REPLACE does a DELETE and INSERT, not an UPDATE. This also means that you must ensure that you define all necessary columns or you will get unexpected NULL values.

Answer 2

"Upsert" is what you want. https://www.sqlite.org/lang_UPSERT.html

UPSERT syntax was added to SQLite with version 3.24.0 (2018-06-04).

CREATE TABLE phonebook2( name TEXT PRIMARY KEY, phonenumber TEXT, validDate DATE ); INSERT INTO phonebook2(name,phonenumber,validDate) VALUES('Alice','704-555-1212','2018-05-08') ON CONFLICT(name) DO UPDATE SET phonenumber=excluded.phonenumber, validDate=excluded.validDate WHERE excluded.validDate>phonebook2.validDate;

Be warned that at this point the actual word "UPSERT" is not part of the upsert syntax.

The correct syntax is

INSERT INTO ... ON CONFLICT(...) DO UPDATE SET...

and if you are doing INSERT INTO SELECT ... your select needs at least WHERE true to solve parser ambiguity about the token ON with the join syntax.

Be warned that INSERT OR REPLACE... will delete the record before inserting a new one if it has to replace, which could be bad if you have foreign key cascades or other delete triggers.

Answer 3

If you have no primary key, You can insert if not exist, then do an update. The table must contain at least one entry before using this.

INSERT INTO Test 
   (id, name)
   SELECT 
      101 as id, 
      'Bob' as name
   FROM Test
       WHERE NOT EXISTS(SELECT * FROM Test WHERE id = 101 and name = 'Bob') LIMIT 1;

Update Test SET id='101' WHERE name='Bob';

Answer 4

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

Answer 5

I believe you want UPSERT.

"INSERT OR REPLACE" without the additional trickery in that answer will reset any fields you don't specify to NULL or other default value. (This behavior of INSERT OR REPLACE is unlike UPDATE; it's exactly like INSERT, because it actually is INSERT; however if what you wanted is UPDATE-if-exists you probably want the UPDATE semantics and will be unpleasantly surprised by the actual result.)

The trickery from the suggested UPSERT implementation is basically to use INSERT OR REPLACE, but specify all fields, using embedded SELECT clauses to retrieve the current value for fields you don't want to change.

Answer 6

INSERT OR REPLACE will replace the other fields (TypeID, Level) to default value.

INSERT OR REPLACE INTO book(id, name) VALUES(1001, 'Programming')

I am using this

INSERT OR IGNORE INTO book(id) VALUES(1001);
UPDATE book SET name = 'Programming' WHERE id = 1001;

You can also use

INSERT OR REPLACE INTO book (id, name) 
VALUES (1001, 'Programming',
  (SELECT typeid FROM book WHERE id = 1001),
  (SELECT level FROM book WHERE id = 1001),
)

but I think the first method more easy to read