Additional languages
« 13.2.4.2 INSERT DELAYED Syntax
13.2.5 LOAD DATA INFILE Syntax »
Section Navigation      [Toggle]

13.2.4.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;

The rows-affected value is 1 if the row is inserted as a new record and 2 if an existing record 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.

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 Carl Furst on May 1 2006 6:09pm[Delete] [Edit]

You can use the condition IF function in the on "Duplicate key update" clause, but you can't use the "values" keyword on the else caluse. It doesn't reconize the field name.

insert into tableA select a, b, c from tableB on duplicate key update tableA.x = if(tableA.y like 'foobar', tableA.y, c);

that works but,

insert into tableA select a, b, c from tableB on duplicate key update tableA.x = if(tableA.y like 'foobar', tableA.y, values(c));

this generates an "Unknown Column in Field list" error.
Interesting.

However what you can do is this:

insert into tableA select a, b, c from tableB on duplicate key update tableA.x = if(values(a), values(a), tableA.x);

which is VERY useful, I find, if I don't want to limit the caluse with a where and don't want to overwrite fields with a blank.

Posted by Jason Kemp on May 12 2006 9:43pm[Delete] [Edit]

What isn't explained above is that you can update more than one column as in a normal stand-alone UPDATE thus:

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

So assuming columns a and b are your primary key you can ensure that you will store these values for c,d and e whether the row with this primary key value is in the table already or not.

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")

Add your own comment.