Page 1 of 1

sending multiple UPDATE queries to mySQL?

Posted: Thu Aug 17, 2006 10:47 am
by fgomez
Hello,

My grasp of mySQL is just strong enough to do the things I need to do, so please bear with me.

I need to send multiple update queries to mySQL (updating a range of records won't do here), and I was wondering if it was possible to send them all at once rather than individually. I tried separating the queries with semi-colons in a variable called $q and then doing

Code: Select all

$result = mysql_query($q) ;
but I got a syntax error.

There are about 500 records to update, and I'd prefer not to ding mySQL 500 times if possible.

Thanks.

Posted: Thu Aug 17, 2006 10:55 am
by JayBird
What are you updating?

Are you updating 500 record to the same value, or are they all different values?

Posted: Thu Aug 17, 2006 10:56 am
by fgomez
All to the same value.

Posted: Thu Aug 17, 2006 10:58 am
by JayBird
well, you could do something like this

Code: Select all

$ids = array(1,2,6,4,9,5,654,546,54,624);

$updateids = implode(",", $ids);

$sql = "UPDATE `yourTable` SET `someField` = 'someValue' WHERE `id` IN(".$updateids.")";

mysql_query($sql);

Posted: Thu Aug 17, 2006 11:01 am
by fgomez
Perfect. I tried searching for IN() on the mySQL doc page, because I figured it would be something like this, but as you can imagine my search results were useless.

Posted: Thu Aug 17, 2006 12:12 pm
by s.dot
Seems a simple where clause would be simpler... if you need it. If you're updating all the records in the table..

Code: Select all

mysql_query("UPDATE `table` SET `foo` = '$bar'") or die(mysql_error());
That would update all the records in the table. If you have a specific condition you need to update on, like where first name = bob, something like this would do:

Code: Select all

mysql_query("UPDATE `table` SET `foo` = '$bar' WHERE `firstname` = 'bob'") or die(mysql_error());
Hope that helps.

Posted: Thu Aug 17, 2006 12:29 pm
by fgomez
Thanks, Scott, but the IN() solution worked for me. I wasn't updating every record, so the first option wouldn't have sufficed, and the second option wouldn't have worked either, because the records I was updating didn't have anything obviously in common.