Multiple row update

I am having a problem updating several rows of a mysql table using a single mysql statement.

I have the rowids i want to update in a string eg.

$ids="id1, id2, id3, id4,...."

and i have my values in another string eg.

$values="str1, str2, str3, str4,....";

(i have more than 30,000 rows to update)

The idea开发者_如何学Python is the row with id1 should be updated with str1 and so on.

How can i fix this?

Thanks

It's going to be a very ugly query but...

update table
set    str = case id
             when id1 then str1
             when id2 then str2
             ...
             end
where  id in (id1, id2, ...)

Create a temp table with two columns, id and str, and do a very long

INSERT INTO temptable VALUES (id1,str1),(id2,str2)...(idn,strn);

You build the string in a for loop without going to the DB. Then you do one UPDATE joining to the temp table in the obvious way. (Indexing the temp table may or may not help.)

You don't want 30K round trips to the DB. (On the other hand, you may have to split the INSERT up if you run into maximum string length issues.)