使用每个单独行的多个“where"子句更新多行 [英] Update multiple rows with multiple 'where' clauses for each individual row

问题描述

我正在尝试像这样更新我的表格:

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

我可以做一个 foreach() 但这会发送超过 50 个单独的查询，这非常慢.这就是为什么我希望将其合并为 1 个大查询.

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.

(我确实为每一行使用了一个 id，但 game_id、x 和 y 的组合是我用什么来识别我需要的行.)

( I do use an id for each row but the combination of game_id, x and y is what I use to Identify the row I need. )

此处描述的 codeIgniter 的 update_batch() 函数:使用 CodeIgniter 更新批处理很有帮助并且几乎完美，但它只允许使用 1 个单个 where 子句，你不能(据我理解和尝试)输入一个包含多个 where 子句的数组.

The update_batch() function from codeIgniter described here: Update batch with CodeIgniter was 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.

我也检查了这个问题:MYSQL UPDATE SET 在同一列但有多个 WHERE 子句但它只允许只包含一个不同的 WHERE 子句的多行更新，我需要多个 WHERE 子句！:)

I've also checked out this question: MYSQL UPDATE SET on the Same Column but with multiple WHERE Clauses But it only allows for multiple row updates containing only a single different WHERE clause and I need multiple WHERE clauses! :)

Anwsers 可以是简单的 SQL 或使用 php(和 CodeIgniter)或以不同的方式.我希望以任何可能的方式解决这个问题；)

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

我真的可以使用建议/帮助！=D

I can really use the advice/help! =D

推荐答案

试试这个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.

这篇关于使用每个单独行的多个“where"子句更新多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！