content_image/696291.jpg content_image/696290.jpg
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 quite identical: For an InnoDB  table where a  is an auto-increment column, the INSERT statement increases the auto-increment value but the 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; 

<img src='content_image/696291.jpg'>

<img src='content_image/696290.jpg'>

## Note 

The use of VALUES()  to refer to the new row and columns is deprecated beginning with MySQL 8.0.20, and is subject to removal in a future version of MySQL. Instead, use row and column aliases, as described in the next few paragraphs of this section. 

Beginning with MySQL 8.0.19, it is possible to use an alias for the row, with, optionally, one or more of its columns to be inserted, following the VALUES  or SET  clause, and preceded by the AS  keyword. Using the row alias new , the statement shown previously using VALUES()  to access the new column values can be written in the form shown here: 

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

