Today at work, I had to create a SQL query that’d insert some rows to a MySQL table. The rows are not user generated, they are dynamically created through several SQL queries. The results of the queries should be entered to a separate table, unless if they’re already inserted.

Imagine there’s a table called “library_lending”, which looks like the following.

CREATE  TABLE `library_lending` (
  `user_name` VARCHAR(45) NOT NULL ,
  `date` DATE NOT NULL ,
  `book_name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `user_date` (`user_name` ASC, `date` ASC) );

Please note that we have created a “Unique Index” (“Unique Key”) called “user_date” that combined both “user_name” and “date” fields. MySQL won’t allow inserting two rows that has same values for “user_name” and “date” fields. In other words, the above table is designed to keep records of people who’re borrowing books from a library, and this table won’t allow anyone to borrow more than one book in a day, because only one record can be inserted with same “user_name” and “date”.

We’re going to write a query that’d insert a row if no duplicate combination of “user_name” and “date” exists, if it does, it will just update the “book_name” to the new one.

First, let’s insert a row into the table like we normally would.

(`user_name`, `date`, `book_name`)
VALUES ('buddy', '2013-12-05', 'The-Hound-of-the-Baskervilles');

This is how our table looks, once it’s done:

A row inserted using INSERT INTO method.
A row inserted using INSERT INTO method.

Then, let’s let the same person try to borrow another book on the same day.


When we try that, MySQL would give us following error.

Error Code: 1062. Duplicate entry 'buddy-2013-12-05' for key 'user_date'

Sure enough, the table still looks exactly like earlier.

Let’s try the magic of ON DUPLICATE KEY UPDATE now.

ON DUPLICATE KEY UPDATE `book_name` = 'A-Study-in-Scarlet';

In simple English, above query says “Try to insert these data to “library_lending” table. If it fails due to a duplicate key, just update the value of field “book_name” to “A-Study-in-Scarlet”, in the row with the duplicate key.

Once that’s done, the table will look like this:

The row is updated using
The row is updated using “ON DUPLICATE KEY UPDATE”.

The “id”, “user_name”, and “date” fields are still the same; just “book_name” has been updated.

Have you used this functionality in MySQL earlier?