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

by

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.

More Related Contents:

  1. How to do a batch insert in MySQL
  2. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  3. MySQL search and replace some text in a field
  4. Can MySQL replace multiple characters?
  5. How to copy a row and insert in same table with a autoincrement field in MySQL?
  6. MySQL Error 1264: out of range value for column
  7. How do I get the ID of multiple inserted rows in MySQL?
  8. How to insert data to MySQL with auto-incremented column(field)?
  9. Replace Into Query Syntax
  10. MySQL INSERT INTO … VALUES and SELECT
  11. MySQL error 1241: Operand should contain 1 column(s)
  12. MySQL optimizing INSERT speed being slowed down because of indices
  13. MySQL for replace with wildcard
  14. MySQL: Insert datetime into other datetime field
  15. “Unknown column in ‘field list'”, but column does exist
  16. Insert and set value with max()+1 problems
  17. INSERT INTO SELECT statement with more columns which is not in another table
  18. MySQL how to fill missing dates in range?
  19. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  20. How to split the name string in mysql?
  21. Why does MySQL allow “group by” queries WITHOUT aggregate functions?
  22. How to set a maximum execution time for a mysql query?
  23. Using group by on two fields and count in SQL
  24. Can MySQL convert a stored UTC time to local timezone?
  25. Whats the fastest way to lookup big tables for points within radius MySQL (latitude longitude)
  26. MySQL CONCAT returns NULL if any field contain NULL
  27. How to get a list of months between two dates in mysql
  28. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  29. Postgres LEFT JOIN with WHERE condition
  30. How to check if field is null or empty in MySQL?

Leave a Comment