Doing UPDATE or INSERT depending on whether a row exists in MySQL

Question

In MySQL, I am trying to find an efficient way to execute UPDATE if a row already exists in a table, or INSERT if a row does not exist.

I found two possible ways:

• Obvious: open a transaction, SELECT to find if a row exists, INSERT if it does not exist, or UPDATE if it exists, commit the transaction
• first INSERT IGNORE to the table (so the error does not occur if the row already exists), then UPDATE

The second method avoids the transaction.

Which, in your opinion, is more effective, and are there any better ways (for example, using a trigger)?

Answer 1

INSERT ... ON DUPLICATE KEY UPDATE

Answer 2

You can also do an UPDATE, check the number of affected rows, if it is less than 1, then it could not find a suitable row, so perforate INSERT.

Answer 3

There is another way: REPLACE .

 REPLACE INTO myTable (col1) VALUES (value1) 

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

Answer 4

There is a REPLACE expression in mysql that I believe does more or less what you want.

Answer 5

REPLACE INTO will be the solution; it uses UNIQUE INDEX to replace or insert something.

 REPLACE INTO yourTable SET column = value; 

Remember that this works differently than you might expect, REPLACE is literal. First, it checks to see if there is a UNIQUE INDEX collision that would prevent INSERT , it deletes ( DELETE ) all the rows that collide, and then the INSERT line you gave it.

This, for example, leads to subtle issues like triggers that don't fire (because they check for an update that never happens), or the values ​​revert to their default values ​​(because you have to specify all the values).

Answer 6

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