[Today I Learned] Insert and Update rows with MySQL “ON DUPLICATE KEY UPDATE”

You can use MySQL’s “ON DUPLICATE KEY UPDATE” to insert and update rows when your query faces a “Duplicate Key” error.

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` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `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.

INSERT INTO
`library_lending`
(`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.

INSERT INTO
`library_lending`
(`user_name`,`date`,`book_name`)
VALUES
('buddy','2013-12-05','A-Study-in-Scarlet');

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.

INSERT INTO
`library_lending`
(`user_name`,`date`,`book_name`)
VALUES
('buddy','2013-12-05','A-Study-in-Scarlet')
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?

6 thoughts on “[Today I Learned] Insert and Update rows with MySQL “ON DUPLICATE KEY UPDATE””

  1. Really useful, but I would like to know how to handle “on duplicate key update” with multiple insert values, this is:
    # create buddy2 entry with ‘A-Study-in-Scarlet2’
    INSERT INTO
    `library_lending`
    (`user_name`,`date`,`book_name`)
    VALUES
    (‘buddy2′,’2013-12-05′,’A-Study-in-Scarlet2’);

    # try to update both buddy and buddy2 book_name to ‘A-Study-in-Scarlet3’ and ‘A-Study-in-Scarlet4’ respectively.
    INSERT INTO
    `library_lending`
    (`user_name`,`date`,`book_name`)
    VALUES
    (‘buddy’,’2013-12-05′,’A-Study-in-Scarlet3′),
    (‘buddy2′,’2013-12-05′,’A-Study-in-Scarlet4’)
    ON DUPLICATE KEY UPDATE `book_name` = X;

    What should be placed on X? I’ve read some documentation and some say VALUES(`book_name`), but it doesn’t seem to work properly.

    Thanks a lot!

Leave a Reply