MySQL 使用多个“where”子句为每一行更新多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15344120/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update multiple rows with multiple 'where' clauses for each individual row
提问by Dex
I am trying to update my table like this:
我正在尝试像这样更新我的表：
Update MyTable
SET value = 1
WHERE game_id = 1,
x =-4,
y = 8
SET value = 2
WHERE game_id = 1,
x =-3,
y = 7
SET value = 3
WHERE game_id = 2,
x = 5,
y = 2
I can do a foreach()but that will send over 50 separate Queries which is very slow.
That's why I want it to be combined into 1 big Query.
我可以做一个，foreach()但这会发送 50 多个单独的查询，这非常慢。这就是为什么我希望将其合并为 1 个大查询。
( I do use an idfor each row but the combination of game_id, xand yis what I use to Identify the row I need. )
（我确实为每一行使用了一个id，但是game_id、x和y的组合是我用来识别我需要的行的。）
The update_batch() function from codeIgniter described here: Update batch with CodeIgniterwas helpful and almost perfect but it only allows for 1 single where clause, you cannot (as far as I understood and tried) enter an array with multiple where clauses.
此处描述的 codeIgniter 的 update_batch() 函数： 使用 CodeIgniter 更新批处理很有帮助且几乎完美，但它只允许 1 个 where 子句，您不能（据我了解和尝试）输入具有多个 where 子句的数组。
I've also checked out this question: MYSQL UPDATE SET on the Same Column but with multiple WHERE ClausesBut it only allows for multiple row updates containing only a single different WHERE clause and I need multiple WHERE clauses! :)
我也检查了这个问题： MYSQL UPDATE SET on the Same Column but with multiple WHERE Clauses但它只允许多行更新只包含一个不同的 WHERE 子句，我需要多个 WHERE 子句！:)
Anwsers can be in simple SQL or with the use of php (and CodeIgniter) or in a different way. I'd this problem to be solved in any possible way ;)
Anwsers 可以是简单的 SQL 或使用 php（和 CodeIgniter）或其他方式。我希望以任何可能的方式解决这个问题;)
I can really use the advice/help! =D
我真的可以使用建议/帮助！=D
回答by John Woo
give this a try by using CASE
尝试使用 CASE
Update MyTable
SET value = CASE
WHEN game_id = 1 AND x = -4 AND y = 8 THEN 1
WHEN game_id = 1 AND x = -3 AND y = 7 THEN 2
WHEN game_id = 2 AND x = 5 AND y = 2 THEN 3
ELSE value
END
WHERE game_ID IN (1,2,3) AND -- the purpose of this WHERE clause
x IN (-4, -3, 5) AND -- is to optimize the query by preventing from
y IN (8,7,2) -- performing full table scan.
