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?

"insert or replace" is **utterly different** from "insert or update" — Fattie, Dec 15 '16 at 17:47
How about [**`UPSERT`**](https://www.sqlite.org/draft/lang_UPSERT.html)? — ashleedawg, Nov 12 '20 at 09:15
Does this answer your question? [SQLite - UPSERT \*not\* INSERT or REPLACE](https://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace) — user4157124, Jul 03 '22 at 12:54

janm · Accepted Answer · 2014-07-02T06:34:43.557

375

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"));

edited Jul 02 '14 at 06:34

answered Sep 03 '10 at 10:55

janm

17,976
1
43
61

133

Wrong "insert or replace" is different than "insert or update". For a valid answer, see http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace – rds Nov 12 '12 at 19:34
17

@rds No, it's not wrong because this question says "modify the fields" and the primary key is not part of the column list, but all of the other fields are. If you are going to have corner cases where you are not replacing all of the field values, or if you are messing around with the primary key you should be doing something different. If you have a complete set of new fields this approach is just fine. Do you have a specific problem I can't see? – janm Nov 13 '12 at 10:39
13

It's valid if you know all the new value for all the fields. If the user update only, say, the `Level`, this approach cannot be followed. – rds Nov 14 '12 at 15:07
4

**Correct**. The other answer is relevant, but this approach is valid for updating all fields (excluding the key). – Ярослав Рахматуллин Feb 22 '14 at 22:42
2

Yes This is Completly wrong. What will happen if just i want to update single column value on Confliction from new one. In above case all other data will be replaced by new one that is not correct. – Mrug Apr 09 '14 at 11:25
Basically trying to pack `insert or ignore ...; update ...` into an `insert or replace` comes with issues and unless you really need to save one line I wouldn't do it. Consider the following table `"""create table if not exists my_table (rowid INTEGER PRIMARY KEY, name STRING, timestamp DEFAULT strftime('%s', 'now'))"""` – user3467349 Jan 15 '15 at 21:13
3

You may wish to add as a pitfall that INSERT OR REPLACE will trigger ON DELETE CASCADE clauses on other tables using the record being replaced as a foreign key. The result is somewhat baffling until you realize that "INSERT OR REPLACE" behaves (as makes sense) as "DELETE IF EXISTS, THEN INSERT" which triggers things that act on record deletion. – Mike Jun 15 '16 at 17:17
1

"insert or replace" is utterly different from "insert or update" – Fattie Dec 15 '16 at 17:48
1

I don't like "insert or replace" because the original record with a unique ID (if have) is removed, and a new record (with a new unique ID) is created. – Ben Lin Apr 28 '18 at 22:37
I doubt that the `sub select` will cause poor performance if there are many such columns requiring `sub select` and the number of INSERTs is large. – Hopeless Jul 25 '18 at 12:19
You do not need to write ```insert or replace```. ```replace``` for it self does the trick. Because the functionality of ```replace``` itself is: First -> delete the existing row that causes a constraint violation. Second -> insert a new row. – Julian Nov 18 '21 at 07:43

score 124 · Answer 2 · edited Sep 20 '18 at 23:12

124

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)

edited Sep 20 '18 at 23:12

imans77

516
5
16

answered Dec 01 '13 at 09:48

moshik

1,424
1
10
6

9

at which moment it will ignore? when the name and age are both the same? – Arnaud Aliès Sep 16 '16 at 08:35
This should be the solution... if you are using any trigger on insert, the accepted answer fires every time. This does not and performs an update only – PodTech.io Nov 16 '16 at 13:24
1

It ignores based solely on the name. Remember that only the "name" column is a primary key. – Gabriel Ferrer Jun 20 '17 at 03:30
4

When the record is new, then the update is not necessary but will be executed anyway, leading to bad performance? – Marcelo Glasberg Nov 01 '17 at 20:53
How to execute a prepared statement for this? – prashant Apr 08 '18 at 18:28
@GabrielFerrer but the "name" column is not a primary key. – EmreAkkoc Sep 03 '18 at 20:13
@prashant According to [this](https://stackoverflow.com/questions/3736984/multiple-sql-statements-in-qsqlquery-using-the-sqlite3-driver) answer, only one sql statement is allowed per prepare – MiB_Coder Oct 22 '20 at 20:35
in update table name is not required here: `INSERT OR IGNORE INTO my_table (name, age) VALUES ('Karen', 34) UPDATE SET age = 34 WHERE name='Karen'` – Firosh Vasudevan Jul 24 '21 at 11:36

score 78 · Answer 3 · answered Jan 27 '11 at 10:54

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.

Steely Wing · Answer 4 · 2023-07-22T07:00:38.920

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

--- sample table ---

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

INSERT INTO Book VALUES (1001, 'C++', 10, 10, 0);

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

--- after INSERT OR REPLACE, other fields are changed to default (null)
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

--- Create record for ID = 1001. If insert failed, just ignore
sqlite> INSERT OR IGNORE INTO Book(ID) VALUES(1001);

--- Set other field value
sqlite> UPDATE Book SET Name='SQLite' WHERE ID=1001;

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

Method 2

Using UPSERT (UPSERT 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').

score 44 · Answer 5 · answered Sep 03 '10 at 10:50

44

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

answered Sep 03 '10 at 10:50

Burcin

973
1
9
25

12

Two operations should be no problem with a transaction at the right isolation level, regardless of the database. – janm Sep 03 '10 at 11:00
7

`Insert or Replace` is really more preferable. – MPelletier Sep 03 '10 at 12:07
2

I really wish IT documentation would contain more examples. I've tried this below and it doesn't work (my syntax is obviously wrong). Any ideas on what it should be? INSERT INTO Book (Name,TypeID,Level,Seen) VALUES( 'Superman', '2', '14', '0' ) ON CONFLICT REPLACE Book (Name,TypeID,Level,Seen) VALUES( 'Superman', '2', '14', '0' ) – SparkyNZ Sep 03 '10 at 19:47
7

Also what if row values are exactly the same, then affected row count will be zero, and a new duplicate row will be created. – barkside Apr 17 '12 at 09:20
If you use surrogate PK then put UNIQUE constraint on column which has natural PK and then no need for worries about duplicate values. In case you use natural PK, then it's all solved with PRIMARY KEY constraint. – vbokan Jun 26 '12 at 14:15
2

+1, because **INSERT OR REPLACE** will delete the original row on conflict and if you are not setting all the columns, you will lose original values – Pavel Machyniak Jul 04 '12 at 12:23
`insert of replace` doesn't always do the trick. I have a local database syncing with a distant one. if I new to create a new item in the distant one but it's not reachable atm I create a new entry with a '-1' id. so replace needing a unique column won't work in this case. a custom test is needed – dvkch Jul 11 '12 at 10:16
@PavelMachyniak but if you do supply all column values while doing an INSERT or REPLACE dont you think it does the trick, do you think INSERT or REPLACE as a query is better or adding it as a constraint during create table is better? – PirateApp Apr 27 '18 at 13:57

score 36 · Answer 6 · edited Mar 25 '19 at 12:41

36

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.

edited Mar 25 '19 at 12:41

jww

97,681
90
411
885

answered Oct 31 '18 at 16:42

ComradeJoecool

734
6
18

It exists in the documentation and I have the lastest version of sqlite which is 3.25.1 but it doesn't work for me – Bentaiba Miled Basma Dec 02 '18 at 19:56
did you try those two queries above verbatim? – ComradeJoecool Dec 04 '18 at 00:59
1

Note that Ubuntu 18.04 comes with SQLite3 **v3.22**, not 3.25, so it doesn't support the `UPSERT` syntax. – starbeamrainbowlabs Aug 17 '19 at 19:23
This works well and is the supported method recommended in the documentation. – AutoBaker Jul 04 '22 at 13:24

matt · Answer 7 · 2016-01-01T18:04:35.360

8

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

edited Jan 01 '16 at 18:04

answered Jan 01 '16 at 16:21

matt

91
1
2

This is the only solution that worked for me without creating duplicate entries. Probably because the table I am using has no primary keys, and has 2 columns with no default values. Even though it is a bit of a lengthy solution, it does the job properly and works as expected. – Inbar Rose Jul 19 '16 at 14:01
1

Move the 'Update' statement before the 'Insert' statement to avoid insert-then-update issue. – Carlos Liu Jul 27 '22 at 07:43

score 4 · Answer 8 · edited May 23 '17 at 12:10

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.

score 1 · Answer 9 · edited May 23 '17 at 10:31

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 IF NOT EXISTS ELSE UPDATE?

9 Answers9

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

If you want to preserve the other field

Linked

Related

INSERT IF NOT EXISTS ELSE UPDATE?

9 Answers9

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

If you want to preserve the other field

Linked

Related

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