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

User · Answer

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.

User · Answer

INSERT OR REPLACE will replace the other fields to default value  sqlite gt  CREATE TABLE Book     ID     INTEGER PRIMARY KEY AUTOINCREMENT    Name   TEXT    TypeID INTEGER    Level  INTEGER    Seen   INTEGER     sqlite gt  INSERT INTO Book VALUES  1001   C     10  10  0    sqlite gt  SELECT   FROM Book  1001 C   10 10 0  sqlite gt  INSERT OR REPLACE INTO Book ID  Name  VALUES 1001   SQLite     sqlite gt  SELECT   FROM Book  1001 SQLite     If you want to preserve the other field  Method 1  sqlite gt  SELECT   FROM Book  1001 C   10 10 0  sqlite gt  INSERT OR IGNORE INTO Book ID  VALUES 1001   sqlite gt  UPDATE Book SET Name  SQLite  WHERE ID 1001   sqlite gt  SELECT   FROM Book  1001 SQLite 10 10 0   Method 2  Using UPSERT  syntax was added to SQLite with version 3 24 0  2018-06-04   INSERT INTO Book  ID  Name    VALUES  1001   SQLite     ON CONFLICT  ID  DO   UPDATE SET Name excluded Name   The excluded  prefix equal to the value in VALUES   SQLite

User · Answer

Upsert is what you want  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 gt 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

User · Answer

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

User · Answer

I think it s worth pointing out that there can be some unexpected behaviour here if you don t thoroughly understand how PRIMARY KEY and UNIQUE interact   As an example  if you want to insert a record only if the NAME field isn t currently taken  and if it is  you want a constraint exception to fire to tell you  then INSERT OR REPLACE will not throw and exception and instead will resolve the UNIQUE constraint itself by replacing the conflicting record  the existing record with the same NAME   Gaspard s demonstrates this really well in his answer above   If you want a constraint exception to fire  you have to use an INSERT statement  and rely on a separate UPDATE command to update the record once you know the name isn t taken

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You should use the INSERT OR IGNORE command followed by an UPDATE command  In the following example name is a primary key   INSERT OR IGNORE INTO my table  name  age  VALUES   Karen   34  UPDATE my table SET age   34 WHERE name  Karen      The first command will insert the record  If the record exists  it will ignore the error caused by the conflict with an existing primary key   The second command will update the record  which now definitely exists

[sqlite] INSERT IF NOT EXISTS ELSE UPDATE?

Examples related to sqlite

Examples related to insert

Examples related to exists

Examples related to upsert

Examples related to merge-conflict-resolution