CODEWITHSUNDEEP
mysqlsqlsql-update
Ogugua Belonwu
Ogugua Belonwu

Reputation: 2141

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 is the row with id1 should be updated with str1 and so on.

How can i fix this?

Thanks

Upvotes: 5

Views: 388

Answers (2)

Andrew Lazarus
Andrew Lazarus

Reputation: 19320

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

Upvotes: 1

Denis de Bernardy
Denis de Bernardy

Reputation: 78443

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, ...)

Upvotes: 1

Related Questions