What are practical differences between `REPLACE` and `INSERT ... ON DUPLICATE KEY UPDATE` in MySQL?

Ivan picture Ivan · Feb 7, 2012 · Viewed 33k times · Source

What I need is to set the values of all the fields of a record with a particular key (the key is composite actually), inserting the record if there is no record with such a key yet.

REPLACE seems as meant to do the job, but at the same time its manual page suggests INSERT ... ON DUPLICATE KEY UPDATE.

What of them should I better choose and why?

The only "side effect" of REPLACE that comes into my mind is that it would increment autoincrement values (fortunately I don't use any) while INSERT ... ON DUPLICATE KEY UPDATE probably wouldn't. What are the other practical differences to take in mind? In what particular cases can REPLACE be preferred over INSERT ... ON DUPLICATE KEY UPDATE and vice versa?

Answer

Mark Byers picture Mark Byers · Feb 7, 2012

REPLACE internally performs a delete and then an insert. This can cause problems if you have a foreign key constraint pointing at that row. In this situation the REPLACE could fail or worse: if your foreign key is set to cascade delete, the REPLACE will cause rows from other tables to be deleted. This can happen even though the constraint was satisfied both before and after the REPLACE operation.

Using INSERT ... ON DUPLICATE KEY UPDATE avoids this problem and is therefore prefered.