(My)SQL - batch update

Question

Hey, I have a table with "id", "name", and "weight" columns. Weight is an unsigned small int.

I have a page that displays items ordered by "weight ASC". It'll use drag-n-drop, and once the order is changed, will pass out a comma-separated string of ids (in the new order).

Let's say there's 10 items in that table. Here's what I have so far:

Sample input:

5,6,2,9,10,4,8,1,3,7

Sample PHP handler (error handlers & security stuff excluded):

<?php
$weight = 0;
$id_array = explode(',', $id_string);

foreach ($id_array as $key => $val)
{
    mysql_query("UPDATE tbl SET weight = '$weight' where id = '$val' LIMIT 1");
    $weight++;
}
?>

When I make a change to column order, will my script need to make 10 separate UPDATE queries, or is there a better way?

Answer 1

Yes, you would need to do 10 updates. There are ways to batch up multiple queries in a single call to mysql_query, but it's probably best to avoid that.

If it's performance you are worried about, make sure you try it first before worrying about that. I suspect that doing 10 (or even 20 or 30) updates will be plenty fast.

Answer 2

You can only specify one where clause in a single query -- which means, in your case, that you can only update one row at a time.

With 10 items, I don't know if I would go through that kind of troubles (it means re-writing some code -- even if that's not that hard), but, for more, a solution would be to :

• delete all the rows
• inserts them all back
• doing all that in a transaction, of course.

The nice point is that you can do several inserts in a single query ; don't know for 10 items, but for 25 or 50, it might be quite nice.

Here is an example, from the insert page of the MySQL manual (quoting) :

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas.
Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Of course, you should probably not insert "too many" items in a single insert query -- an insert per 50 items might be OK, though (to find the "right" number of items, you'll have to benchmark, I suppose ^^ )

Answer 3

10 updates is the simplest way conceptually. if you've got a bazillion rows that need to be updated, then you might have to try something different, such as creating a temporary table and using a JOIN in your UPDATE statement or a subquery with a row constructor.

Answer 4

You could create a temporary table with the new data in it (i.e., id and weight are the columns), then update the table with this data.

create temporary table t (id int, weight float);

insert into t(id, weight) values (1, 1.0), (2, 27), etc

update tbl inner join t on t.id = tbl.id
set tbl.weight = t.weight;

So, you have one create statement, one insert statement, and one update statement.