INSERT ... ON DUPLICATE KEY UPDATE Syntax

f 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;

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) 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;