Sqlite: Insert if not exist, Update if exist

Question

I have a database with 2 tables like this:

cg_resp
id  | name   | email
1   | George | george@yahoo.com

id column is primary_key,autoincremented and name is unique

and

equip_info
id  | description  | cg_resp_id 
1   | Caliper      | 1

In the application form I have 2 edit boxes named edit_resp_name and edit_resp_email

If user insert a new responsible name like John with the email john@yahoo.com then during the save of form I would like to insert a new responsible into cg_resp table, get the last inserted id and update it to equip_info.cg_resp_id.

If the user maintain the name George but it's updating the email like george01@gmail.com then I would like to update the id = 1 from cg_resp with the new email address and the rest of them (equip_info.cg_resp_id and cg_resp.id) to remain the same.

I would like to maintain the original reference of cg_resp_id from table equip_info if the name of responsible is the same, so it's necessary to avoid situation like delete and insert a new one.

How can be done this in one Sqlite sql sequence?

Is it absolutely necessary to do this in one sql statement? You could try to `select` the name from your database. If the resulting set is empty you use an `insert` statement, if the set contains the name you call the `update` statement. — JusticeJuice, Feb 01 '15 at 09:07
It's not absolutely necessary... if possible, otherwise I will do it classical. — REALSOFO, Feb 01 '15 at 09:32
Does this answer your question? [SQLite UPSERT / UPDATE OR INSERT](https://stackoverflow.com/questions/15277373/sqlite-upsert-update-or-insert) — Steen Schütt, May 10 '21 at 23:10

score 4 · Answer 1 · answered Feb 01 '15 at 12:22

SQLite has no built-in mechanism that would not delete the existing row.

The easiest way to do this is to put the logic into your application:

cursor = db.execute("SELECT ...")
if cursor.empty:
    db.execute("INSERT ...")
else:
    db.execute("UPDATE ...")

If your language allows to read the number of affected rows, it is possible to do this with two SQL commands:

db.execute("UPDATE ...")
if db.rowcount == 0:
    db.execute("INSERT ...")

score 1 · Accepted Answer · answered Feb 01 '15 at 09:53

1

Use INSERT OR REPLACE which does exactly what you want : insert a new row when the name does not exists or update otherwise.

answered Feb 01 '15 at 09:53

Jérôme Radix

10,285
4
34
40

5

REPLACE deletes the old row if it exists. – CL. Feb 01 '15 at 12:19
Be careful, INSERT OR REPLACE deletes the row and inserts a new one with a new key, so if you have autogenerated keys and leverage them, you will be out of luck. I haven't found an elegant way to do what you're asking yet but will keep looking. – Shahin May 07 '19 at 04:15

Sqlite: Insert if not exist, Update if exist

2 Answers2

Linked

Related