Semper Solutions' Blog

If you need to insert rows into your table, but don’t want to check, is that value exists or not in table, there is very useful feature of MySQL - INSERT ... ON DUPLICATE KEY UPDATE syntax.

• Normally if you want to check row, you have to run SELECT query and if row does not exist, run INSERT query (and you need to use 2 queries).
• You may use REPLACE INTO… syntax, but it firstly tries to delete old row and then inserts new one, so you can lose your autoincremented ID (updated row has another ID) and it can cause a lot of problems with dependencies.

The solution is:
INSERT ... ON DUPLICATE KEY UPDATE syntax.

I use it that way:
INSERT INTO tbl1 (ArticleName, ArticleBody) VALUES ('Article1', 'Body1')
ON DUPLICATE KEY UPDATE ArticleName = 'Article1', ArticleBody = 'Body1';

or more useful if you synchronize two tables:
INSERT INTO tbl1(ArticleName, ArticleBody) SELECT ArticleName, ArticleBody FROM tbl2
ON DUPLICATE KEY UPDATE ArticleName = tbl2.Article1, ArticleBody = tbl2.ArticleBody;
Note: One of that fields has to have UNIQUE index.

First run of the query inserts records. Second run inserts new records and updates existing records. If your source table grows, running this query synchronizes tables in very simple way.

---

Another advantage of this syntax is running counters. For example, if we have table like:
CREATE TABLE views (
IP VARCHAR( 100 ) NOT NULL ,
ViewCounter INT NOT NULL ,
UNIQUE (IP)
) ENGINE = MYISAM

IP is UNIQUE, so we can always count our page’s views:

INSERT INTO views (IP, ViewCounter) VALUES ('192.168.0.1', 1)
ON DUPLICATE KEY UPDATE ViewCounter = ViewCounter + 1;

First run inserts record with value 1. Every next run of this query increases the counter

If you want to count daily views, you can use it that way:
CREATE TABLE Views (
IP VARCHAR( 100 ) NOT NULL ,
ViewDate DATE NOT NULL ,
ViewCounter INT NOT NULL ,
UNIQUE (IP, ViewDate)
) ENGINE = MYISAM

IP and ViewDate are UNIQUE pair, so we count our page’s views:

INSERT INTO views (IP, ViewDate, ViewCounter) VALUES ('192.168.0.1', '2009-07-01', 1)
ON DUPLICATE KEY UPDATE ViewCounter = ViewCounter + 1;

Enjoy
Alex