- 1 month ago 9
MySQL Question

Is it possible to update multiple rows at the time using Zend_Db_Table?

I have a simple array.

$a = array(
array('id' => 2, 'year' => 2010),
array('id' => 3, 'year' => 2011)

And i have a MySQL table with
primary key. Now I update my table like this:

foreach ($a as $v) {
$db->update($v, array('id = ?' => $v['id']));

And my
contains 3700 rows. I need to create one big query instead of loop. How can do it? Thank you in advance. Sorry for my english.


What your asking isn't often done, as you would usually use update() to either set a lot of records to have the same values or set one record to have many differnt values.

One way around this is the aggregate the updates, so using your array get all the ids where the year is 2011 then run this:

 $where = array();

 // This where should contain all the ids that need the year set to 2011
 // E.g.
 $where[] = array("id" => 3);

 $db->update("table_name", array("year" => 2011), $where);

Doing this will reduce the number of queries assuming you have many rows with the same year. The documentation for this is here.

Or you could use a method like this

Edit after OP response

The very nature of the problem means it cannot be solved effeicently.

Your asking for a way of updating 3,700 rows of data with very different sets of data. If the sets of data are different then there is no pattern that you can exploit in order to make this effeicent. Finding patterns, like rows with the same year, and using them to your advantage will increase the speed of the query but will require some brain enagement in the form of array mashing as noted by regilero.