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;