I have a table on my site where the user has the ability to re-order the rows. This part is easy to do with jQuery UI, and getting the new order back is simple using the $('#sortable').sortable("serialize") method. My question is now, what's the most efficient way to save all these records in the database, since changing the order of one item changes all the records in the table? I know I could create a new UPDATE statement for each row based on their ID, but I feel like there has to be a better way. Is there?
I'm using MySQL and ASP.NET.
Use the START TRANSACTION command. This is much faster.