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 this, you will define an amount column and update it whenever a new payment comes in:

mysql> REPLACE INTO customers VALUES (1,'Mike','Christensen','America');
Query OK, 2 rows affected (0.03 sec)

You can see that two rows are affected, one duplicate row is deleted and a new row is inserted:

mysql> INSERT INTO payments VALUES('Mike Christensen', 200) ON DUPLICATE KEY UPDATE payment=payment+VALUES(payment);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO payments VALUES('Ravi Vedantam',500) ON DUPLICATE KEY UPDATE payment=payment+VALUES(payment);
Query OK, 1 row affected (0.01 sec)

When Mike Christensen pays $300 next time, this will update the row and add this payment to the previous payment:

mysql> INSERT INTO payments VALUES('Mike Christensen', 300) ON DUPLICATE KEY UPDATE payment=payment+VALUES(payment);
Query OK, 2 rows affected (0.00 sec)

VALUES (payment): refers to the value given in the INSERT statement. Payment refers to the column of the table.