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…

Accepted Answer

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.
```

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

Solution

Problem

Related problems