MySQL insert or update using ON DUPLICATE KEY UPDATE

MySQL insert or update - one of the most common problems when it comes to database. We want to check if the record exists in the database, if not we want to update it. Let's take statistics example. We would like to insert IP Address if it's not in database or update view's count if it's already in there. Simple? Sure! We can first make SELECT query for the given record. If it's in database we update the record, if not we insert new one. Simple, right? But we can do it with single query using ON DUPLICATE KEY UPDATE

ON DUPLICATE KEY UPDATE - introduction

This is really simple and easy in use. Let's take a look on some really simple example. Let's assume that we want to collect number of views for the article in our website.

It will be as simple as possible, I just want to show You how to use ON DUPLICATE KEY UPDATE:)

CREATE TABLE `stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `article_id` int(11) NOT NULL,
  `views_count` int(11) NOT NULL DEFAULT '1',
  `created` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

We create simple table with 4 columns:

id - auto-incremented unique ID number
article_id - ID of our article
views_count - number of views of the article
created - when stats info was created. To show You more advanced usage of ON DUPLICATE KEY UPDATE lets save only the date without time.

So when we load the page we want to insert row into table if it's not exists. Otherwise we want to increase views_count field by 1. As I wrote before, we can do two queries. SELECT and INSERT or UPDATE. But let's use ON DUPLICATE KEY UPDATE.

First of all we have to have UNIQUE key if we want to use ON DUPLICATE KEY UPDATE. It's a must, we have to have some key that we want to perform check on. So let's think, which field we want to be unique? It's obviously article_id.

Let's add the key then by executing this query:

ALTER TABLE  `stats` ADD UNIQUE uk_article_id(
`article_id`
);

After that query we will have UNIQUE key named uk_article_id. MySQL will generate name of the key by itself, but good practice is to name Your key. It will be simple to remove it for instance.

Alright, we have our unique key. So let's create really simple query that will insert the data to database or update the existing one.

INSERT INTO stats (article_id, created) VALUES 
	(12, CURRENT_DATE())
ON DUPLICATE KEY UPDATE
	views_count = views_count + 1

If we execute such query for the first time we will have single record with our article_id = 12, views_count = 1 (1 is default value, if not given), and current date.

If we execute this query several times we will still have single record in database but views_count value will be increased. Really simple, isn't it?

Multiple keys in ON DUPLICATE KEY UPDATE

Let's upgrade our concept a little bit. Let's say that we want to have daily statistics for each article. We can't use our example as there is only creation date of the first insert. How to solve this situation? We have to have separate rows for each day.

First of all we have to delete our previous key as it won't be needed anymore. If we named our index we can simply execute this query to remove the unique key:

DROP INDEX uk_article_id ON stats

If we didn't name our key, we don't know how to delete it. Fastest option is to list all keys in our table, like that:

SHOW INDEX FROM stats

This query will list all keys in our table. In column Key_name there will be the name of the key we want to delete.

OK, we are ready to add new key to the table with article_id and created columns. To add UNIQUE key for two or more columns we have to execute such query:

ALTER TABLE  `stats` ADD UNIQUE uk_article_id(
`article_id`, `created`
);

Now, if we execute our insert or update query, we will see that we can add multiple article_id rows with the same value, but date in created field must be unique. We won't have two exactly same rows with article_id and created date. For testing purposes You can change CURRENT_DATE() function to the particular date, for instance "2012-12-12" to see how it really works.

Conditional ON DUPLICATE KEY UPDATE with IF

Sometimes there might be need to make additional IF on update process. For instance we want to update our record only if another passed column has different value or something. Let's take a look at this example:

INSERT INTO stats (article_id, created) VALUES 
	(12, CURRENT_DATE())
ON DUPLICATE KEY UPDATE
	views_count = IF(VALUES(article_id) = 12, views_count + 2, views_count + 1) /*,
	additional_field = additional_field + 1,
	next_field = IF(article_id = 1, 1, 0)

Let's say that we want to "cheat" a little bit and increase views_count by two on article_id = 12. We have to add IF statement. It has three parts, condition, value if true, value if false.

In condition we use VALUES() function, which returns given value for the field. Which mean that VALUES(article_id) will return 12, because we passed this value in INSERT statement above. So this condition will be true and views_count will be increased by 2. We can pass multiple IF statements both with update multiple values. Code in commented section have some simple examples.

Keep in mind, that on first insert, when there's no record in the table, insert will be performed. It means that all code after ON DUPLICATE KEY UPDATE won't be executed. For instance in our example when we don't have such values in database, after this query we will have row with article_id = 12 and views_count = 1 (not 2).

Batch insert with ON DUPLICATE KEY

There is also option to make batch insert to database using ON DUPLICATE KEY UPDATE. It will execute update for each given row if there is duplication. Here is some simple example:

INSERT INTO stats (article_id, created) VALUES 
	(13, CURRENT_DATE()),
	(14, CURRENT_DATE()),
	(15, CURRENT_DATE())
ON DUPLICATE KEY UPDATE
	views_count = views_count + 1

If You want read a little bit more about ON DUPLICATE KEY and rest of the stuff I wrote about here are some external links to MySQL manual: