How to use ON DUPLICATE KEY for multiple entries?

Question

In a single entry, we can use ON DUPLICATE KEY to UPDATE the value:

INSERT INTO table
(title, number) VALUES ('$title', '$amount')
ON DUPLICATE KEY UPDATE number=number+$amount

How to use `ON DUPLICATE KEY for multiple entries as

INSERT INTO table
(title, number) VALUES 
('$title1', '$amount1'), 
('$title2', '$amount2'),
('$title3', '$amount3')
.....

score 1 · Accepted Answer · answered May 18 '12 at 19:30

1

You can use the values clause:

ON DUPLICATE KEY UPDATE 
   number = values(number),
   title = values(title)

answered May 18 '12 at 19:30

how to add `$amount1`, `$amount2`, `$amount3` .... (the values come out of PHP) to the current values of the columns? – Googlebot May 18 '12 at 19:39
@Ali: sorry, I don't do PHP, why don't you try something like `number = number + values(number)` – May 18 '12 at 20:48
That's it! How could be so blind not to see this! Thanks for clarification. – Googlebot May 19 '12 at 00:23

1 Answers1