difference b/w REPLACE INTO and INSERT ON DUPLICATE KEY UPDATE

The purpose for using those 2 syntacs is the same, "update a record if the record exist, otherwise insert a record."
But they work slightly in different way.

REPLACE INTO will replace record as it reads. When the PK is used to search record, the record will be simple updated.
But if other unique keys than PK is used, the record will be deleted and insert a new record. It means it will generate new auto number if the key is set to auto_increment.

On the other hand, INSERT ON DUPLICATE KEY UPDATE will simply update all the time.

Both are very useful unless you use them in wrong way.

Comment