ON DUPLICATE KEY UPDATE is a useful feature in MySQL that allows you to update an existing row in the table when you do INSERT.

The table must have a UNIQUE key or PRIMARY key constraint defined which MySQL can use to check if duplicate value exists. If it does, an insert won't occur (as it would violate unique constraint) but instead MySQL will perform an UPDATE of the existing row. This is much more efficient in terms of coding than checking (SELECT query) whether a record exists, then launch either an UPDATE or an INSERT query depending on the value returned from the first SELECT query.

Syntax of ON DUPLICATE KEY UPDATE in INSERT statement:

INSERT INTO table_name
    (column1,column2,column3,...)
VALUES
    (value1,value2,value3,...)
ON DUPLICATE KEY UPDATE
    column_1 = value_1X,
    column_2 = value_2X,
    ......

In this tutorial, we'll use a table called email_sent_count to demonstrate the various features of INSERT...ON DUPLICATE KEY UPDATE. The table tracks how many times an email address has been contacted.

Use script shown below to create the table and populate our test data.

/*Table structure for table `email_sent_count` */ CREATE TABLE `email_sent_count` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `SentCount` int(10) unsigned DEFAULT NULL, `SentTime` timestamp NULL DEFAULT NULL, `HashValue` char(32) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `Uidx_Email` (`Email`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; /*Data for the table `email_sent_count` */ insert into `email_sent_count`(`Email`,`SentCount`,`SentTime`,`HashValue`) values ('james@abcdmnxyz.com',26,'2016-03-06 10:39:01','e10cddc0d6698deb042e3936f65f306b'),('tony@abcdmnxyz.com',5,'2016-04-11 11:40:21','c9defc67f7f3ef0894e85ec9ebc92f0a'),('mary@abcdmnxyz.com',63,'2016-06-25 10:06:31','dccef8d7fa0e574837be7de9bc31359f'),('sam@abcdmnxyz.com',22,'2016-11-09 09:15:19','e55d22d7915ccd303f78b1120c0cf089');

When using INSERT... ON DUPLICATE KEY UPDATE..., consider the following guidelines.

1. The ON DUPLICATE KEY UPDATE clause can contain multiple column updates, separated by commas.
   Please note that the UNIQUE key or PRIMARY key cannot be an AUTO_INCREMENT column which is always unique by nature. That is, if the table only contains the AUTO_INCREMENT unique column and has no other unique key(s) exists, when ON DUPLICATE KEY UPDATE is used, a new record will always be inserted regardless of whether duplicates are found or not.

   See Practice #1.

2. Regardless of whether or not a row is inserted or updated, the INSERT ... ON DUPLICATE KEY UPDATE always increments the auto-increment column by 1.

3. ON DUPLICATE KEY UPDATE can also use a compound unique key/index to check for duplication.

   A compound unique index is created when the values of multiple columns combined together are unique. For example, in Northwind Traders database, a unique index exists on OrderID and ProductID column in order_details table.

4. ON DUPLICATE KEY UPDATE checks every unique index in the table. In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes because it could cause multiple rows getting updated. One exception is that an AUTO_INCREMENT primary key column is generated and maintained by MySQL when the insert happens, so it won't be checked for uniqueness. In other words, in addition to the auto increment primary key column, it's better to have only one unique index (compound or not doesn't matter) in the table.

   Note that beginning with MySQL version 5.5.24, an INSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is marked as unsafe.

5. You can use the VALUES(col_name) function in the UPDATE clause to refer to another column value from the INSERT portion of the statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name. See Practice #2.

6. If you use INSERT IGNORE, the existing record is updated anyway. In other words, IGNORE keyword takes no effect on the ON DUPLICATE KEY UPDATE. See Practice #3.

Practice #1: Insert with multiple updates

The email column is unique. ID column is auto increment which is also unique, but checking if a record exists is based on email column rather than the ID column.

Email address tony@abcdmnxyz.com already exists in the table, so it will trigger an update.

INSERT INTO email_sent_count(Email, SentCount, SentTime, HashValue) 
VALUES('tony@abcdmnxyz.com', 9, now(), 'c9defc67f7f3ef0894e85ec9ebc92f0a')
ON DUPLICATE KEY UPDATE SentCount=SentCount+15,SentTime=now()

Please note that, after executing the above query, even though it updated only one record and no new record was inserted, the output message still showed that "2 rows affected".

Before insert, SentCount was 5:

As the screenshot shown below, the SentCount was increased to 20 for tony@abcdmnxyz.com, and no new record inserted.

After insert, SentCount was updated to 20:

Practice #2: VALUES(col_name) function in the UPDATE clause

In this query, the column HashValue stores a MD5 value (32 characters) and the md5 is to be re-calculated by using the value in the SentTime column. Note that convert function is used to convert the timestamp value to a string value because md5 function only accepts a string value.

INSERT INTO email_sent_count(Email, SentCount, SentTime, HashValue) 
VALUES ('mary@abcdmnxyz.com',68,now(),'dccef8d7fa0e574837be7de9bc31359f')
ON DUPLICATE KEY UPDATE HashValue=md5(convert(VALUES(SentTime), char))

Before insert, mary@abcdmnxyz.com's HashValue was dccef8d7fa0e574837be7de9bc31359f:

After insert, HashValue was updated to a1068734fc5b553e754c33b22dfd3ccb:

Practice #3: Insert with IGNORE

IGNORE keyword has no effect on the ON DUPLICATE KEY UPDATE.

In this query, the IGNORE keyword is redundant because the existing record for tony@abcdmnxyz.com was updated.

INSERT IGNORE INTO email_sent_count(Email, SentCount, SentTime, HashValue) 
VALUES('tony@abcdmnxyz.com', 9, now(), 'c9defc67f7f3ef0894e85ec9ebc92f0a')
ON DUPLICATE KEY UPDATE SentCount=SentCount+15,SentTime=now()

IGNORE keyword only indicates that if the record already exists in the table, discard the insert. The query below does not use ON DUPLICATE KEY UPDATE clause, so the record was not inserted.

INSERT IGNORE INTO email_sent_count(Email, SentCount, SentTime, HashValue) 
VALUES('tony@abcdmnxyz.com', 9, now(), 'c9defc67f7f3ef0894e85ec9ebc92f0a')

Happy coding!

Other tutorials in this category

1. Update Statement in MySQL

2. How to update data in a specific order in MySQL

3. How to update top N rows of data in MySQL

4. Use outer join in update

5. How to do cross-table update in MySQL - Part 1

6. How to do cross-table update in MySQL - Part 2

7. Using Bulk Insert Statement

8. Basic insert statement

9. How to create and use AUTO_INCREMENT column and use LAST_INSERT_ID function

10. Using MySQL REPLACE (INTO) to mimic DELETE + INSERT

11. Update multiple rows (distinctive by primary key) with different values in one query

12. Update multiple rows (distinctive by primary key) for multiple columns in one query

Back to Tutorial Index Page