update multiple rows w/single query - possible?

[cgraz]

I was just wondering if it's possible to update multiple rows in a MySQL database with a single query?

I'm updating a single column based on a WHERE clause obviously, I just want to avoid having to run a foreach loop and then executing n number of queries.

Suppose this is the query:

UPDATE tbl SET update_field = '$value' WHERE id = '$id'

And I'm holding an array where the key holds the update_field and the value holds the id.

Anyone know if this is possible?

[Piranha]

In some cases you can update more than one row, but normally not. You can do like this:

UPDATE tbl SET update_field = '$value' WHERE id = '$id' OR id = '$id'

UPDATE tbl SET update_field = '$value' WHERE non_id = '$value2'

But I don't mean you think those cases. I think you mean something that will work in the same way as many rows, as below:

UPDATE tbl SET update_field = '$value1' WHERE id = '$id1'
UPDATE tbl SET update_field = '$value2' WHERE id = '$id2'

Then the answer is no, it is not possible to update that way. It is not possible to send many queries to the database with a semicolon between from PHP either.

[Lars_Berg]

It is feasible if you have a few values, but will be cumbersome as the number increases.

update t
 set c = case id when $id1 then '$value1' 
                         when $id2 then '$value2' end
  where id in ($id1,$id2)

[cgraz]

thanks Lars. I didn't even know that was possible.

I can see it being cumbersome to write by hand, however, if I wrote an update query like that dynamically, would it place more load on MySQL than if I were to have multiple update queries? Would there be any speed differences between the two alternatives?

Also, is there some sort of limit where I would want to avoid using this syntax and just run a loop with multiple updates? updating 10, 20, 50 rows?