Performing an UPDATE or INSERT depending whether a row exists or not in MySQL

Question

In MySQL, I'm trying to find an efficient way to perform an UPDATE if a row already exists in a table, or an INSERT if the row doesn't exist.

I've found two possible ways so far:

1. The obvious one: open a transaction, SELECT to find if the row exists, INSERT if it doesn't exist or UPDATE if it exists, commit transaction
2. first INSERT IGNORE into the table (so no error is raised if the row already exists), then UPDATE

The second method avoids the transaction.

Which one do you think is more efficient, and are there better ways (for example using a trigger)?

Answer 1

You could also perform an UPDATE, check the number of rows affected, if it's less than 1, then it didn't find a matching row, so perfom the INSERT.

Answer 2

INSERT ... ON DUPLICATE KEY UPDATE

Answer 3

In mysql there's a REPLACE statement that, I believe, does more or less what you want it to do.

Answer 4

If you're doing a lot of these, it might be worth writing them to a file, and then using 'LOAD DATA INFILE ... REPLACE ...'

Answer 5

There is another way - REPLACE.

REPLACE INTO myTable (col1) VALUES (value1)

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 12.2.5, “INSERT Syntax”.

Answer 6

REPLACE INTO would be a solution, it uses the UNIQUE INDEX for replacing or inserting something.