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

Asked

Viewed 610 times

6

I am with the following doubt what is the difference between using REPLACE INTO or ON DUPLICATE KEY UPDATE in mysql, do not both serve the same purpose to make a change to a field or more in the database? If the goal is someone else to enlighten me.

What is the best option to use by the way?

mysql sql

1 answer

7


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

  • If you have a foreign key restriction pointing to that line - Replace will fail.
  • If your external key is configured for cascading deletion, the Replace will cause the rows of other tables to be deleted
  • The uninformed fields will have lost information, precisely because it deletes the row if it exists and inserts another at the end of the table only with the fields and data passed in the query.

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

  • Another problem of REPLACE INTO is that the uninformed fields will have lost information, precisely because it deletes the row if it exists and inserts another at the end of the table only with the fields and data passed in the query.

  • good observation added the answer, thanks. @Robertofagundes

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.