Menu
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?
348
The REPLACE policy will create the record or replace it entirely if a previous version of the record already existed. This will delete potential already existing bins for this record. The UPDATE policy will create the record or update the record if it already exists.
ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE. The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's CLIENT_FOUND_ROWS flag is set.
So yes it is atomic in the sense that if the data that you are trying to insert will cause a duplicate in the primary key or in the unique index, the statement will instead perform an update and not error out.
When you insert a new row into a table if the row causes a duplicate in UNIQUE index or PRIMARY KEY , MySQL will issue an error. However, if you specify the ON DUPLICATE KEY UPDATE option in the INSERT statement, MySQL will update the existing row with the new values instead.
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.
183
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
