25

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

Community
  • 1
  • 1
Dex
  • 704
  • 2
  • 9
  • 22

1 Answers1

52

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.
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Thank you! I'll go and try it out now! – Dex Mar 11 '13 at 17:05
  • be sure to backup your database first `:D` – John Woo Mar 11 '13 at 17:05
  • 2
    Just wanted to add: if you don't include the last line of `CASE` namely `ELSE value`, then bad things are going to happen. The query will match everything inside the `WHERE` clause and whatever doesn't satisfy any `WHEN` condition will be set to `NULL` – Hristo Hristov Mar 01 '20 at 11:16