insert or update confusion

Question

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

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.