What is the difference between REPLACE INTO or ON DUPLICATE KEY UPDATE

Navigation
6

I'm wondering what's the difference between using REPLACE INTO or ON DUPLICATE KEY UPDATE in mysql, are not both for the same purpose making a change to a field or more in the database? If the goal is someone else who enlightens me.

What is the best option to use now?

    
asked by anonymous 04.10.2017 / 14:07

1 answer

5

The Replace performs two actions, it first performs deletion after insertion, this can cause some problems like:

  • If you have a foreign key constraint pointing to this line - Replace will fail.
  • If your foreign key is set to cascade delete, Replace will cause rows from other tables to be deleted
  • Uninformed fields will have information lost, just because it deletes the row if it exists and inserts another one at the end of the table with just the fields and data passed in the query.

Already using INSERT ... ON DUPLICATE KEY UPDATE this problem does not occur, therefore it is recommended that you choose it.

    
04.10.2017 / 14:34
Verification of object state invariance between the execution of private methods How popular is a model from a DataReader in C #?