MYSQL，多次插入和ON DUPLICATE UPDATE [英] 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?

谢谢大家

推荐答案

每个INSERT只能有一个ON DUPLICATE KEY:

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

(当然，您也可以使用VALUES进行此操作；我更喜欢SELECT，因为它更通用.)

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

这篇关于MYSQL，多次插入和ON DUPLICATE UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！