INSERT … ON DUPLICATE KEY UPDATE

June 6, 2007

MySQL has some nifty extensions to ANSI SQL. Two of which are “INSERT … ON DUPLICATE KEY UPDATE” and “REPLACE” (As of MySQL 4.1). These statements are used when inserting data into a table where the unique key of the row you are inserting may already exist, and if it does, you want this row to be updated with new data. Instead of doing a select/insert/update routine, this gives a significant performance increase for frequently updated schemas; and what is even more important, the application code becomes simpler, less error prone, and easy to read.

“INSERT … ON DUPLICATE KEY UPDATE” will actually perform either an UPDATE or an INSERT, whichever one is necessary. The “REPLACE” statement will actually delete the row if it exists and insert the new row.

Example of “INSERT … ON DUPLICATE KEY UPDATE” (assuming column ‘a’ is the unique key):

1
2
3
INSERT INTO TABLE (a,b,c)
VALUES (1,2,3)
ON DUPLICATE KEY UPDATE b=2, c=3;

Example of “REPLACE”:

1
2
REPLACE INTO table_a (a,b,c)
VALUES(1,2,3);

Oracle 9i+ also has an extension that can somewhat similarly handle this conditional INSERT-or-UPDATE task: the MERGE statement, though not as intuitive and the code is not as easy to read. Here is an example of how one might implement MERGE for this situation:

1
2
3
4
5
6
7
8
9
10
11
MERGE INTO SCHEMA.table_a TA
USING (
 SELECT '1' a, '2' b, '3' c
 FROM DUAL
 ) E
ON (TA.a = E.a)
WHEN MATCHED THEN
 UPDATE SET TA.b = E.b, TA.c = E.c
WHEN NOT MATCHED THEN
 INSERT (TA.a, TA.b, TA.c)
 VALUES (E.a, E.b, E.c);

Comparing Oracle’s 11 line method to MySQL’s 3 line method, I must hand it to MySQL for its simpler and superior method.

References:
forums.devshed.com, dev.mysql.com, dev.mysql.com, www.mysqlperformanceblog.com, www.oracle.com

2 Responses to “INSERT … ON DUPLICATE KEY UPDATE”

  1. This officially makes me hate Oracle.

  2. use ANSI SQL 99 and be happy!

Leave a Reply