insert or update confusion

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 se开发者_JAVA百科e a different solution on every answer there is to this problem. Whats the most efficient or easiest way? EDIT: I meant sqlite, sorry

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.

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