Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.5Mb
PDF (A4) - 31.6Mb
PDF (RPM) - 30.1Mb
EPUB - 7.7Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.6Mb
HTML Download (RPM) - 6.5Mb
Eclipse Doc Plugin (TGZ) - 8.3Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 181.3Kb
Man Pages (Zip) - 292.4Kb
Info (Gzip) - 2.8Mb
Info (Zip) - 2.8Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  INSERT ... ON DUPLICATE KEY UPDATE Syntax 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, MySQL performs an UPDATE of the old row. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have similar effect:

INSERT INTO table (a,b,c) VALUES (1,2,3)

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

(The effects are not identical for an InnoDB table where a is an auto-increment column. With an auto-increment column, an INSERT statement increases the auto-increment value but UPDATE does not.)

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

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

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 UPDATE 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 ... ON DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY 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)

That statement is identical to the following two statements:

INSERT INTO table (a,b,c) VALUES (1,2,3)
INSERT INTO table (a,b,c) VALUES (4,5,6)

If a table contains an AUTO_INCREMENT column and INSERT ... ON DUPLICATE KEY UPDATE inserts or updates a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value.

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

Because the results of INSERT ... SELECT statements depend on the ordering of rows from the SELECT and this order cannot always be guaranteed, it is possible when logging INSERT ... SELECT ON DUPLICATE KEY UPDATE statements for the master and the slave to diverge. Thus, in MySQL 5.6.4 and later, INSERT ... SELECT ON DUPLICATE KEY UPDATE statements are flagged as unsafe for statement-based replication. With this change, such statements produce a warning in the log when using statement-based mode and are logged using the row-based format when using MIXED mode. In addition, beginning with MySQL 5.6.6, an INSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe. (Bug #11765650, Bug #58637) See also Section, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.

Prior to MySQL 5.6.6, an INSERT ... ON DUPLICATE KEY UPDATE on a partitioned table using a storage engine such as MyISAM that employs table-level locks locked all partitions of the table. (This did not and does not occur with tables using storage engines such as InnoDB that employ row-level locking.) In MySQL 5.6.6 and later, such statements lock only those partitions in which a partitioning key column is updated. See Section 19.6.4, “Partitioning and Locking”, for more information.

User Comments
  Posted by on March 7, 2007
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
Regarding the trick for making LAST_INSERT_ID() well defined for updates:

INSERT INTO table (a,b,c) VALUES (1,2,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
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:
Now, SELECT LAST_INSERT_ID(); will return the correct ID.

  Posted by Jon Webb on September 21, 2007
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
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
  Posted by Frank Denis on January 30, 2008
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,

  Posted by Michael Austin on February 23, 2008
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));

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 Malcolm Cook on June 9, 2008
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
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
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:
  Posted by Nikolay Pelov on May 18, 2011
This won't work:


Taking value that will be inserted with VALUES() only works with INSERT INTO .... VALUES (...), ...
  Posted by HungNghiep Tran on October 16, 2011
Here is a nice tip for INSERT INTO SELECT ON DUPLICATE KEY UPDATE. Better than Jon Webb's example mentioned above.
The trick is to use user-defined variable to store computed data, so that it is not need to be computed again.
This will also solve Nikolay Pelov's problem in the previous post.

into _Rank_Author(idAuthor, publicationCount, citationCount, coAuthorCount)
select ap.idAuthor, @publicationCount := count(distinct ap.idPaper), 0, 0
from author_paper ap
group by ap.idAuthor
on duplicate key update publicationCount = @publicationCount;
  Posted by Lane Snider on February 22, 2012
I don't see it documented, but it looks like NULL values do not trip the "duplicate key" feature.

Consider a unique key definition like this:
UNIQUE KEY `friend_id` (`friend_id`,`type`,`status`)
Putting in two rows with identical values in those three fields should obviously not be allowed, but it can happen if the field value happens to be NULL in both rows.

A bug? A case of me missing the documentation? Whatever. There it is.
  Posted by Rami Jamleh on May 2, 2012
well it's cant be safe and OR is not AND
when we say A=1 OR B=5 limit 1
and we have the following table
A | B
1 | 6
1 | 5

then it well update the first row only where b = 6

  Posted by Proxy . on August 31, 2012
Note that ON DUPLICATE KEY UPDATE checks every unique fields in table, not just PRIMARY key (page_id for example). So care when you have other unique fields (page_seo_url for example).
  Posted by Geoff Kendall on October 21, 2012
Probably obvious to more experienced users, but the number of rows affected can be 0 as well as the 2 or 1 values mentioned above, if the ON DUPLICATE KEY UPDATE does not change the existing column values.
  Posted by Ivan Levashew on June 9, 2013
Don't forget that UPDATE in MySQL is not standard-conformant. Old values being referenced in UPDATE are getting replaced one by one from left to right to their new values. This is counter-intuitive given that VALUES are being applied simultaneously and UPDATE expressions are not.
  Posted by Jonas Reinhardt on March 19, 2014
When using ON DUPLICATE KEY in combination with a BEFORE INSERT trigger note that if you update a NEW.col_name value in the BEFORE INSERT trigger this will effect the value och values(col_name) in then ON DUPLiCATE KEY UPDATE statement!
Updates to other NEW.col_name in the BEFORE INSERT trigger that is not used in the values(col_name) statement are discarded.
A testcase is available at!2/b324a/1
  Posted by Sam Daams on September 19, 2014
If you have an autoincrement pk, and a unique key on say an email address, and the 'on duplicate update' triggers based on the email address, note that the last_insert_id' will NOT be the autoincrement value of the updated row. It appears to be the most recently inserted autoincrement value. This makes a huge difference. To work around this, use the workaround from 5.1 and earlier: id=LAST_INSERT_ID(id) in the updating query.
  Posted by Rick James on January 13, 2016
Given a simple "normalization table":
UNIQUE (name)
The following will insert a new row or discover the existing id:

One drawback: It will "burn" ids. That is, it grabs the next auto_inc value before realizing that it might not be needed. This can lead to a rude surprise if you are careful to use, say, TINYINT, because you know there won't ever be many "names".
Sign Up Login You must be logged in to post a comment.