MySQL 8 Cookbook

REPLACE, INSERT, ON DUPLICATE KEY UPDATE

There are many cases where you need to handle the duplicates. The uniqueness of a row is identified by the primary key. If a row already exists, REPLACE simply deletes the row and inserts the new row. If a row is not there, REPLACE behaves as INSERT.

ON DUPLICATE KEY UPDATE is used when you want to take action if the row already exists. If you specify the ON DUPLICATE KEY UPDATE option and the INSERT statement causes a duplicate value in the PRIMARY KEY, MySQL performs an update to the old row based on the new values.

Suppose you want to update the previous amount whenever you get payment from the same customer and concurrently insert a new record if the customer is paying for the first time. To do ...