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 | [email protected]

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 [email protected] 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 [email protected] 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?

Answer 1

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