MYSQL, multiple insert and ON DUPLICATE UPDATE

问题:

I'm really blocked with multiple insert values and if any one exist, do custom update. Here is my query that not work :

INSERT INTO `table` (f1,f2,status) VALUES 
(1,5,'on') ON DUPLICATE KEY UPDATE status='off', 
(3,2,'on') ON DUPLICATE KEY UPDATE status='off', 
(15,20,'on') ON DUPLICATE KEY UPDATE status='off';

There is any solution that can do this query?

Thanks for everyone

回答1:

You can only have one ON DUPLICATE KEY per INSERT:

INSERT INTO `table`(f1, f2, status)
    SELECT 1 ,5, 'on' UNION ALL 
    SELECT 3, 2, 'on' UNION ALL
    SELECT 15, 20, 'on'
    ON DUPLICATE KEY UPDATE status = 'off';

(Of course, you can also do this with VALUES; I just prefer SELECT because it is more general.)