Updating many rows in the same table.

[eluser]tmcw[/eluser]
Currently I'm working on reordering a list in CodeIgniter, and the method I'm doing right now is allowing a free javascript-drag-sorting of the list, and then collecting each ID and associating it with a weight, which is the place in the order for that element. It seems like a pretty simple and straightforward thing to do.

The problem is that the queries required to reorder the list, even for the smallest of changes are O(n) - on query per item. Is there any way to either group these queries (does that make a performance difference?) or construct one query that sets 1 column of a table based off of another column?

For instance
setting in a table,

Code:

ID  Weight
1   2
2   1

In one query?

Thanks for any help!

[eluser]MaDe[/eluser]
Hi tmcw,

I'm rather sure (99%) that this is not possible in one statement. You have to update several rows in a way, that can't be expressed by a formula (like: SET ´weight´ = ´weight´ +1) as the user decides the sorting of the list items. Updating multiple rows to "random" values can only be done by updating each row seperately. You might use a foreach loop for that.

I would not concern about the performance issue. If you update only the weight column, that should not have noticable impact on the database as the user will not sort more than 50 item, will he? I would try to do that a different way if I had more than 1,000 updates per page call (but what user will sort a list of 1,000 entries?).

Regards,
MaDe