[sqlite] 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?

This question is related to sqlite insert exists upsert merge-conflict-resolution

The answer is


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)


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>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.


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.


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


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.


INSERT OR REPLACE will replace the other fields to default value.

sqlite> CREATE TABLE Book (
  ID     INTEGER PRIMARY KEY AUTOINCREMENT,
  Name   TEXT,
  TypeID INTEGER,
  Level  INTEGER,
  Seen   INTEGER
);

sqlite> INSERT INTO Book VALUES (1001, 'C++', 10, 10, 0);
sqlite> SELECT * FROM Book;
1001|C++|10|10|0

sqlite> INSERT OR REPLACE INTO Book(ID, Name) VALUES(1001, 'SQLite');

sqlite> SELECT * FROM Book;
1001|SQLite|||

If you want to preserve the other field

  • Method 1
sqlite> SELECT * FROM Book;
1001|C++|10|10|0

sqlite> INSERT OR IGNORE INTO Book(ID) VALUES(1001);
sqlite> UPDATE Book SET Name='SQLite' WHERE ID=1001;

sqlite> 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').


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';

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.


Examples related to sqlite

getting " (1) no such column: _id10 " error Laravel: PDOException: could not find driver auto create database in Entity Framework Core How to open .SQLite files Accessing an SQLite Database in Swift When does SQLiteOpenHelper onCreate() / onUpgrade() run? Attempt to write a readonly database - Django w/ SELinux error Android sqlite how to check if a record exists How can I add the sqlite3 module to Python? "Insert if not exists" statement in SQLite

Examples related to insert

How to insert current datetime in postgresql insert query How to add element in Python to the end of list using list.insert? Python pandas insert list into a cell Field 'id' doesn't have a default value? Insert a row to pandas dataframe Insert at first position of a list in Python How can INSERT INTO a table 300 times within a loop in SQL? How to refresh or show immediately in datagridview after inserting? Insert node at a certain position in a linked list C++ select from one table, insert into another table oracle sql query

Examples related to exists

Select rows which are not present in other table How to fix "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS" error? Check if record exists from controller in Rails sql: check if entry in table A exists in table B How to exclude records with certain values in sql select SQL - IF EXISTS UPDATE ELSE INSERT INTO Linq select objects in list where exists IN (A,B,C) PL/pgSQL checking if a row exists How to Check if value exists in a MySQL database MongoDB: How to query for records where field is null or not set?

Examples related to upsert

PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL? SQLite UPSERT / UPDATE OR INSERT Insert into a MySQL table or update if exists Postgres: INSERT if does not exist already INSERT IF NOT EXISTS ELSE UPDATE? SQLite "INSERT OR REPLACE INTO" vs. "UPDATE ... WHERE" Insert, on duplicate update in PostgreSQL? How do I UPDATE a row in a table or INSERT it if it doesn't exist? SQLite - UPSERT *not* INSERT or REPLACE

Examples related to merge-conflict-resolution

Why does git say "Pull is not possible because you have unmerged files"? Git resolve conflict using --ours/--theirs for all files Cannot checkout, file is unmerged How to interactively (visually) resolve conflicts in SourceTree / git INSERT IF NOT EXISTS ELSE UPDATE? How do I fix a merge conflict due to removal of a file in a branch? Resolving a Git conflict with binary files How to resolve merge conflicts in Git repository?