Performing an UPDATE or INSERT depending whether a

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

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

REPLACE INTO
    yourTable
SET
    column = value;

Please be aware that this works differently from what you might expect, the REPLACE is quite literally. It first checks if there is a UNIQUE INDEX collision which would prevent an INSERT, it removes (DELETE) all rows which collide and then INSERTs the row you've given it.

This, for example, leads to subtle problems like Triggers not firing (because they check for an update, which never occurs) or values reverted to the defaults (because you must specify all values).

Answer 3

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 4

INSERT ... ON DUPLICATE KEY UPDATE

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

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