MySQL - Updating and incrementing a column value, starting from 1, for a range of rows based on and sorted by another column

I have this table:

id  track_name   datetime             weight
1   aName        2010-06-01 09:00:00  1
2   theName3     2010-07-01 11:00:00  2
3   heyThere     2010-08-01 16:00:00  3
4   abcd         2010-08-01 22:44:00  4
5   g123go       2010-08-01 22:00:50  5
6   foobar       2010-09-01 13:11:00  6
7   barfoo       2010-11-01 12:00:55  7
8   barbar       2010-12-01 11:11:00  8

The weight determines the row record order. It is used for ordering a playlist. And the user can move items up and down, thus reordering in the simple fashion, in which works great.

No开发者_Python百科w I wonder if there is possible to write a single query that can change the 'weight' value based on the 'date' column, ordering by either DESC or ASC. The same for the 'track_name' column.

Example pseudo query:

UPDATE table SET weight (start from 1) ORDER BY datetime ASC

My alternative is to fetch all rows and process each and everyone of them on the web server, which I doubt is the most effecient way, if there are thousands of records.

I don't think you can do it with a single query. You need a counter, this means you need a loop. If you want to do it with MySQL only, you can create a stored procedure. If not, just write a PHP script (witch might will be a bit slower). Logic is the same:

1. Get all the data from the table;
2. Loop through every record in the order you need and update weight parameter.

You could use a temporary table with an auto incremented id and select insert into it using your order.