14.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
, MySQL performs an UPDATE
of the old row. For example, if column a
is declared as UNIQUE
and contains the value1
, the following two statements have similar 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 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.