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?
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.