Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 26.9Mb
PDF (A4) - 26.9Mb
PDF (RPM) - 25.3Mb
HTML Download (TGZ) - 6.3Mb
HTML Download (Zip) - 6.3Mb
HTML Download (RPM) - 5.4Mb
Man Pages (TGZ) - 158.0Kb
Man Pages (Zip) - 261.3Kb
Info (Gzip) - 2.6Mb
Info (Zip) - 2.6Mb
Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  INSERT ... ON DUPLICATE KEY UPDATE Syntax

13.2.5.2 INSERT ... ON DUPLICATE KEY UPDATE Syntax

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

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

UPDATE t1 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.)

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

UPDATE t1 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.

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 the mysql_real_connect() C API function when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

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 ON DUPLICATE KEY UPDATE clause can contain multiple column assignments, separated by commas.

In assignment value expressions in the ON DUPLICATE KEY UPDATE clause, you can use the VALUES(col_name) function 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 the ON DUPLICATE KEY UPDATE clause or INSERT statements and returns NULL otherwise. Example:

INSERT INTO t1 (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 t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=3;
INSERT INTO t1 (a,b,c) VALUES (4,5,6)
  ON DUPLICATE KEY UPDATE c=9;

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

For INSERT ... SELECT statements, these rules apply regarding acceptable forms of SELECT query expressions that you can refer to in an ON DUPLICATE KEY UPDATE clause:

  • References to columns from queries on a single table, which may be a derived table.

  • References to columns from queries on a join over multiple tables.

  • References to columns from DISTINCT queries.

  • References to columns in other tables, as long as the SELECT does not use GROUP BY. One side effect is that you must qualify references to nonunique column names.

References to columns from a UNION do not work reliably. To work around this restriction, rewrite the UNION as a derived table so that its rows can be treated as a single-table result set. For example, this statement is problematic:

INSERT INTO t1 (a, b)
  SELECT c, d FROM t2
  UNION
  SELECT e, f FROM t3
ON DUPLICATE KEY UPDATE b = b + c;

Instead, use an equivalent statement that rewrites the UNION as a derived table:

INSERT INTO t1 (a, b)
SELECT * FROM
  (SELECT c, d FROM t2
   UNION
   SELECT e, f FROM t3) AS dt
ON DUPLICATE KEY UPDATE b = b + c;

The technique of rewriting a query as a derived table also enables references to columns from GROUP BY queries.

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.5.18 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 error log when using statement-based mode and are written to the binary using the row-based format when using MIXED mode. In addition, beginning with MySQL 5.5.24, 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 17.1.2.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.

An INSERT ... ON DUPLICATE KEY UPDATE on a partitioned table using a storage engine such as MyISAM that employs table-level locks locks all partitions of the table. This does not occur with tables using storage engines such as InnoDB that employ row-level locking. This issue is resolved in MySQL 5.6. For more information, see Section 19.5.4, “Partitioning and Table-Level Locking”.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Nikolay Pelov on May 18, 2011
This won't work:

INSERT INTO xxx(id,val) SELECT a,b FROM yyyy ON DUPLICATE KEY UPDATE val = VALUES(val)

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.

insert
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 http://sqlfiddle.com/#!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 Rocky Shams on November 6, 2016
How to implement the following using the INSERT ... ON DUPLICATE KEY UPDATE Syntax

IF (NOT EXISTS(SELECT * FROM cio_master)
BEGIN
INSERT INTO cio_alert (Client_RFID_Number)
SELECT (tcio_master.Client_RFID_Number)
From cio_master tcio_master
WHERE ((tcio_master.Client_Check_Out = NULL AND tcio_master.Client_Check_In = NULL)
OR (tcio_master.Client_Check_Out != NULL AND tcio_master.Client_Check_In = NULL))
AND NOT EXISTS(SELECT Client_RFID_Number FROM cio_alert tcio_alert
WHERE tcio_alert.Client_RFID_Number = tcio_master.Client_RFID_Number)


END
ELSE
BEGIN
UPDATE cio_alert
SET
Client_Check_Out= (SELECT cio_master.Client_Check_Out FROM cio_master WHERE cio_alert.Client_RFID_Number=cio_master.Client_RFID_Number),
Client_Check_In= (SELECT cio_master.Client_Check_In FROM cio_master WHERE cio_alert.Client_RFID_Number=cio_master.Client_RFID_Number),
NotInOut= IF(( SELECT Client_Std_CI_Time FROM ciom_master WHERE (ciom_master.Client_RFID_Number=cio_alert.Client_RFID_Number)
AND (CURTIME() > Client_Std_CI_Time + INTERVAL 30 MINUTE)), 'Not In',
IF((SELECT Client_Std_CO_Time FROM ciom_master WHERE (ciom_master.Client_RFID_Number=cio_alert.Client_RFID_Number)
AND (CURTIME() > Client_Std_CO_Time + INTERVAL 30 MINUTE)), 'Not Out', NotInOut))
WHERE cio_alert.Client_RFID_Number = (SELECT Client_RFID_Number FROM cio_master
WHERE cio_alert.Client_RFID_Number = cio_master.Client_RFID_Number
AND ((cio_master.Client_Check_Out = NULL AND cio_master.Client_Check_In = NULL)
OR (cio_master.Client_Check_Out != NULL AND cio_master.Client_Check_In = NULL)))

END
Sign Up Login You must be logged in to post a comment.