Update multiple rows with multiple 'where' clauses for each individual row

Question

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.

( 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. )

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.

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

I can really use the advice/help! =D

Answer 1

give this a try by using 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.