insert or update confusion

Question

Hi everyone, I'm a bit confused about how to handle the situation where you update a row in sqlite if it exists, or insert it if it doesn't. I seem to see a different solution on every answer there is to this problem. Whats the most efficient or easiest way? EDIT: I meant sqlite, sorry

Answer 1

IF EXISTS may be helpful here, having you update if the condition yields true or insert if false.

Answer 2

For sqlite:

CREATE TABLE foo (bar INTEGER UNIQUE, baz INTEGER)
INSERT INTO foo (bar,baz) VALUES (1, 2)

This should cause an IntegrityError: column bar is not unique:

INSERT INTO foo (bar,baz) VALUES (1,3)

But with sqlite you can upsert by doing INSERT OR REPLACE:

INSERT OR REPLACE INTO foo (bar,baz) VALUES (1,3)

Instead of inserting a new row, since a row with bar equaling 1 already exists, the value of baz is updated to 3.

---

For MySQL:

Create a UNIQUE index on the table. Then use

INSERT INTO table (...) values (...) ON DUPLICATE KEY UPDATE field = ...

for example:

CREATE UNIQUE INDEX mytable_index ON mytable (field1,field2)

INSERT INTO mytable (field1,field2,field3) values (val1,val2,val3) 
    ON DUPLICATE KEY UPDATE field3 = val3

This requires that (field1,field2) pairs in mytable be unique. Then when you insert, either you will get a new row, or, if (val1,val2) already exist in mytable, the value of field3 will be updated.

Answer 3

Sounds like you're after MySQL's ON DUPLICATE KEY UPDATE syntax, but with more information on the table and what you want to happen because you might be after MySQL's REPLACE syntax instead.

In the past, the functionality has been referred to as an "upsert", but now has it's own ANSI syntax called MERGE (not supported by MySQL at this time).