CODEWITHSUNDEEP
sqlsqlite
REALSOFO
REALSOFO

Reputation: 862

Sqlite: Insert if not exist, Update if exist

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?

Upvotes: 3

Views: 10260

Answers (2)

CL.
CL.

Reputation: 180020

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

Upvotes: 4

Jérôme Radix
Jérôme Radix

Reputation: 10533

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

Upvotes: 1

Related Questions