0

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

EDIT:

Sorry the question wasn't clear enough, I just changed it!

Community
  • 1
  • 1
Dex
  • 704
  • 2
  • 9
  • 22
  • Please don't repost questions. – BoltClock Mar 11 '13 at 18:55
  • The anwser is posted here for people who have the same problem: http://stackoverflow.com/questions/15344120/update-multiple-rows-with-multiple-where-clauses-for-each-individual-row – Dex Mar 11 '13 at 21:09

2 Answers2

0

It seems me you need the logical operator AND:

UPDATE MyTable
SET value = 1
WHERE game_id = 1
AND x         = -4
AND y         = 8
Étienne Miret
  • 6,448
  • 5
  • 24
  • 36
0

I'm not familiar with the multiple WHERE clauses how you have it laid out, why not just do

UPDATE MyTable 
SET value     = 1 
WHERE game_id = 1
AND x         = -4
AND y         = 8
Matt Busche
  • 14,216
  • 5
  • 36
  • 61