Skip navigation links
12.2.6 LOAD DATA INFILE Syntax »
Section Navigation      [Toggle]

12.2.5.3. INSERT ... ON DUPLICATE KEY UPDATE Syntax

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect: 

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

UPDATE table SET c=c+1 WHERE a=1;

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated.

If column b is also unique, the INSERT is equivalent to this UPDATE statement instead: 

UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

If a=1 OR b=2 matches several rows, only one row is updated. In general, you should try to avoid using an ON DUPLICATE KEY clause on tables with multiple unique indexes.

The ON DUPLICATE KEY UPDATE clause can contain multiple column assignments, separated by commas.

You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... UPDATE statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in INSERT ... UPDATE statements and returns NULL otherwise. Example: 

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

That statement is identical to the following two statements: 

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=3;
INSERT INTO table (a,b,c) VALUES (4,5,6)
  ON DUPLICATE KEY UPDATE c=9;

If a table contains an AUTO_INCREMENT column and INSERT ... UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful. However, you can work around this by using LAST_INSERT_ID(expr). Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful for updates, insert rows as follows: 

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

The DELAYED option is ignored when you use ON DUPLICATE KEY UPDATE.


User Comments

Posted by Bart Alewijnse on April 4 2006 4:39pm[Delete] [Edit]

A slightly simpler example, counting words:

Given a table like:
CREATE TABLE wordcount (word varchar(80) primary key, count integer);
...you can repeat:
INSERT INTO wordcount (word,count) VALUES ('a_word',1) ON DUPLICATE KEY UPDATE count=count+1;
The first time it'll insert, the rest it'll update.

Posted by Ilya Evseev on May 19 2006 3:05pm[Delete] [Edit]

Whats happen with new record that conflicts with an existing one?
It seems like it's missed in space:
-----------------------------------------------------
create database if not exists testdups;
use testdups;
drop table if exists test1, test2;

create table test1 (
id int not null auto_increment primary_key,
a varchar(16),
b varchar(16)
);

create table test2 (
id int not null auto_increment primary_key,
a varchar(16),
b varchar(16)
);

insert table1(a, b) values
('a1', 'b1'),
('a1', 'b2'),
('a1', 'b2'),
('a1', 'b3')
);

alter table test2 add unique ab(a, b);

insert into test2 select * from test1 on duplicate key update a = 'REMOVE-ME';

mysql> select * from test2;

+----+-----------+------+
| id | a         | b    |
+----+-----------+------+
|  1 | a1        | b1   |
|  2 | REMOVE-ME | b2   |
|  4 | a1        | b3   |
+----+-----------+------+

Posted by Dionysis Zindros on September 16 2006 7:54am[Delete] [Edit]

According to http://dev.mysql.com/doc/refman/5.0/en/mysql-affected-rows.html, when you use mysql_affected_rows() (for example, if you use PHP, or the equivalent function in your language) to detect the number of affected rows of an insert-on-duplicate, it won't always return what expected.

Take the following example:

INSERT INTO mytable (primaryid, count) VALUES(5, 1) ON DUPLICATE KEY UPDATE count = count + 1;

Assuming primaryid is defined as a primary key, in case the value 5 doesn't exist, it will be inserted and, as expected, the affected rows will be 1. In case 5 already exists, however, an update will be made. In the latter case, affected rows will return 2, which is not what one would normally expect. So keep this in mind when checking for affected rows :-)

Posted by Shane McEwan on October 27 2006 4:09am[Delete] [Edit]

Here is an example of how to update multiple columns using values supplied in the INSERT statement. This assumes that column 'a' is the unique key.

INSERT INTO table (a,b,c,d,e) VALUES (1,2,3,4,5) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c), d=VALUES(d), e=VALUES(e);

This also works for multiple rows:

INSERT INTO table (a,b,c,d,e) VALUES (1,2,3,4,5), (6,7,8,9,10) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c), d=VALUES(d), e=VALUES(e);

If you have a lot of columns it would be nice if you could use the following syntax:

INSERT INTO table (a,b,c,d,e) VALUES (1,2,3,4,5) ON DUPLICATE KEY UPDATE VALUES(b,c,d,e);

and it would match the columns you want to update with the values in the INSERT. Unfortunately, this does not work. You MUST explicitly provide each column assignment.

Posted by winzou on November 1 2006 3:53pm[Delete] [Edit]

Be carefull when doing "INSERT INTO .. ON DUPLICATE KEY" with negative value on unsigned column !

Example :
CREATE TABLE gp_unt (
unt_id tinyint(3) unsigned NOT NULL,
unt_nb smallint(5) unsigned NOT NULL,
PRIMARY KEY (unt_id),
KEY unt_nb (unt_nb)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO gp_unt (unt_id, unt_nb) VALUES (1, 6)

And then, if you want to update the col unt_nb to 5 (in other words, make 6 - 1), you will try this query :

INSERT INTO gp_unt (unt_id, unt_nb) VALUES (1, -1)
ON DUPLICATE KEY UPDATE unt_nb = unt_nb + VALUES(unt_nb)

But, as the unt_nb column is unsigned, MySQL returns an error which says that 5 is out of range (here, negative). It's wrong of course.

For the moment, you have to let the column signed.

Posted by [name withheld] on March 7 2007 5:16pm[Delete] [Edit]

It is not clearly stated in the documentation above, but if there is a single multiple-column unique index on the table, then the update uses (seems to use) all columns (of the unique index) in the update query.

So if there is a UNIQUE(a,b) constraint on the table in the example, then the INSERT is equivalent to this UPDATE statement:

UPDATE table SET c=c+1 WHERE a=1 AND b=2;

(and not "a=1 OR b=2")

Posted by Martin Stjernholm on July 14 2007 2:09am[Delete] [Edit]

Regarding the trick for making LAST_INSERT_ID() well defined for updates:

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

This does not work if nothing changes, presumably because MySQL doesn't run the dummy update at all then. I.e. if there is an existing record with 3 in column c then LAST_INSERT_ID() still won't return the AUTO_INCREMENT id afterwards.

I'm not sure whether this should be regarded as a bug or not, but it does make the construct less useful.

Posted by Sigge Mannen on September 14 2007 7:48pm[Delete] [Edit]

A way to make things work is to use a dummy column,
so if you have a table with auto_increment column ID and unique key a,b and a smallint dummy column for instance, the query might look like this:
INSERT INTO test (a,b) VALUES ('1','2') ON DUPLICATE KEY UPDATE ID=LAST_INSERT_ID(ID),Dummy = NOT dummy;
Now, SELECT LAST_INSERT_ID(); will return the correct ID.

Posted by Jon Webb on September 21 2007 11:13am[Delete] [Edit]

Example:Update-Select-GroupBy
I have a table of totals 'v8totals' with PrimaryKey=(tid,tyy,tmm) and a table of records 'bbprepay'.
Here's how i keep my totals uptodate when the prepays change...

INSERT INTO v8totals (tid,tyy,tmm,finances)
SELECT '3218',YEAR(ppdate),MONTH(ppdate),SUM(ppamount) FROM bbprepay
WHERE fkuserid='3218' GROUP BY YEAR(ppdate),MONTH(ppdate)
ON DUPLICATE KEY UPDATE finances=(SELECT SUM(ppamount) FROM bbprepay
WHERE fkuserid='3218' AND tyy=YEAR(ppdate) AND tmm=MONTH(ppdate) GROUP BY YEAR(ppdate),MONTH(ppdate))

It might not be the best way to do an "Insert otherwise Update" but its working for me. Hope it helps. :)

Posted by Ondrej Maly on October 23 2007 11:25am[Delete] [Edit]

Another nice trick (suppose tbl_a.a and tbl_a.b form an unique index):

INSERT INTO tbl_a (a,b,c)
SELECT a,b,c FROM tbl_b
ON DUPLICATE KEY UPDATE c = tbl_b.c

Posted by Frank Denis on January 30 2008 2:52pm[Delete] [Edit]

If you need to update/insert a field and atomically get the previous value, here's a way to do the trick:

SET @previous_note := NULL;

INSERT INTO rencontre_note_moi_last_votes (id, note) VALUES (1, 2) ON DUPLICATE KEY UPDATE note = IF((@previous_note := note) <> NULL IS NULL, VALUES(note), NULL);

SELECT @previous_note;

Two tricks are actually used here :

(anything) <> NULL is always NULL even if (anything) is NULL. So (anything) <> NULL IS NULL is always TRUE.

@previous_note is set according to the value of a field, and that value is obviously the previous value, not the one being currently computed.

That way, a new "note" is inserted of the "note" is changed, and the previous value is returned.

Best regards,

-Frank.

Posted by Michael Austin on February 23 2008 1:06am[Delete] [Edit]

Another useful hint at INSERT with UPDATE:

create table b (a1 integer,a2 integer, primary key (a1));

insert into b values (1,2),(2,2);

select * from b;

+----+------+
| a1 | a2   |
+----+------+
|  1 |    2 |
|  2 |    2 |
+----+------+

insert into b (a1, a2) values(1,2) on duplicate key
update b.a2 = IF(VALUES(a2) < b.a2,b.a2,VALUES(a2));

Translated:
IF new value is less than old value use old value else use new value;


mysql> select * from b;
+----+------+
| a1 | a2   |
+----+------+
|  1 |    2 |
|  2 |    2 |
+----+------+

insert into b (a1, a2) values(1,3) on duplicate key
update b.a2 = IF(VALUES(a2) < b.a2,b.a2,VALUES(a2));

select * from b;
+----+------+
| a1 | a2   |
+----+------+
|  1 |    3 |
|  2 |    2 |
+----+------+

Posted by William Lou on April 9 2008 1:04am[Delete] [Edit]

Add to Dionysis Zindros's comments:
mysql_affected_rows() works for simple update or insert query. When using update query, if update columns with their old values, mysql_affected_rows will return 0 (as we expected). However, in "INSERT INTO .... ON DUPLICATE KEY UPDATE" query, for updates with real changes, it returns 2 (as Dionysis Zindros's comments); for updates with no real changes (update with the same old values), it also returns 2. Temporary conclusion is: mysql_affected_rows's result doesn't make sense for "INSERT INTO ... ON DUPLICATE KEY UPDATE ..." query.

Posted by Malcolm Cook on June 9 2008 6:32pm[Delete] [Edit]

The 'work around' suggested in the documentation to obtain the ID of a row updated using the DUPLICATE KEY UPDATE clause of an INSERT statement has a problem in addition to those mentioned by earlier posts. Namely, if you are using INNODB storage engine and have a FOREIGN KEY referencing the primary key of the table being updated, this strategy may fail with:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails ....

My ultimate approach is to not use the DUPLICATE KEY UPDATE for this purpose. Rather, I explicitly test for existence using a SELECT, and only perform the INSERT if the SELECT fails.

Posted by Rob Smeets on July 19 2008 11:30am[Delete] [Edit]

Doing SELECT and then INSERT is not a complete replacement of INSERT ON DUPLICATE KEY UPDATE: there is a race condition involved.

You will still need to check whether your INSERT works, and if it doesn't then you need to do an update.

Rob Smeets

Posted by Michael McLaughlin on May 26 2009 12:09am[Delete] [Edit]

While it probably goes without saying, you need to include the auto increment column or you can cause an insertion anomaly with an overriding signature in the INSERT statement.
I'd suggest something here with a code example.

I'm told it's not a bug:

http://bugs.mysql.com/bug.php?id=45081

Add your own comment.