Insert or update confusion

Question

I'm a bit confused about how to handle the situation where you are updating a row in sqlite if it exists, or inserting it if it does not work. It seems I see a different solution for each answer to this problem. What is 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 result in the IntegrityError: string not being 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 a bar equal to 1 already exists, the baz value is updated to 3.

---

For MySQL:

UNIQUE .

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

:

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

, (my1, field2) mytable . , , (val1, val2) mytable, 3 .

Answer 2

IF EXISTS, , true insert, false.