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;
The DELAYED option is ignored when you use
ON DUPLICATE KEY UPDATE.
