Most efficient mySQL batch update query?

Forum Moderators: open

Message Too Old, No Replies

Most efficient mySQL batch update query?

All the fields are the same except the rows to be update

salnajjar

5:28 pm on Jun 28, 2008 (gmt 0)

I'm trying to write a SQL query that will update a number of rows in a single table.

The update itself is very simple, it changes a single tiny-int (fake boolean) value from 0 to 1, the column is always the same, but the rows to be updated are a variable (set by a PHP array of values).

What is the most efficient mySQL query to achieve this? I'm hoping to avoid putting in a loop to run a single query to update each row individually.

My data structure is such (all non-relevant fields removed):

Table: messages
Fields: messageid, deleted

Current basic query:
UPDATE `messages` SET messages.deleted = '1' WHERE messages.messageid = arrayvalue

I'm wondering if it's possible to perform the equivalent of an "or" in the "WHERE" part of the mySQL query, but all my google searches tell me I'm out of luck.

Any opinions?

Thanks for any help

Seri

salnajjar

7:07 pm on Jun 28, 2008 (gmt 0)

Just in case anyone else in the future is battling this, I still haven't managed to find a way of performing one big query to update all the rows, but the most efficient way I've found of performing a bunch of updates on a mySQL db dictated by a PHP array is:

foreach ($arrayvariable as $i => $value) {
$query = "UPDATE `table` SET `field` = 'value' WHERE `otherfield` = '$arrayvariable[$i]'";
mysql_query($query) or die();
}

brotherhood of LAN

7:51 pm on Jun 28, 2008 (gmt 0)

have you looked into setting up a temporary table?

1. Insert unique key/where values into a temp table
2. UPDATE using a join between the temp table and the table to be updated.