根据另一列更新MySql列

Hey guys I am still pretty new to MySQL and I just ran into a problem that I can't seem to figure out.

Say I have a table called "tracks" with the following columns and sample data

track_hash | track_order 
abc        | 1
abc        | 2
abc        | 3
abc        | 4
def        | 1
def        | 2
ghi        | 1

So the point is that when I display the tracks, the tracks should be ordered by the track order. So if i want to display all tracks from abc, it will display it based on the track order (1, 2, 3). Track hash "def" has two tracks...etc.

So currently in my DB I just have an empty track_order column. How would I go about to filling the track_order column with the correct data?

You can do this with update and a user defined variable. However, you have a fundamental problem. SQL tables represent unordered sets. So, there is no inherent ordering in the table, unless a column specifies the order.

Let me assume there is a column called id. Then the following does what you want:

update tracks t
    set t.track_order = if(@th = t.track_hash, (@rn := coalesce(@rn, 0) + 1),
                           if(@th := t.track_hash, @rn := 1, @rn := 1)
                          )
    order by t.track_hash, t.id;

You don't have to initialize the variables for this to work, but you can initialize them before the update.

I think you might be looking for user defined variables.

You could do something like this:

SET @t1=0; 
insert into `tablename` (track_order) values(@t1 := @t1+1) where id = some_id

I'm not entirely sure how you would go about doing it for every record in your database. I think this should work, but this does it on a per id basis (you could make it track_hash if that would be better). Not sure if that suits you?

You'll have to do it manually for every track_hash this way, so if you have a lot of different track_hash records it might be worth figuring out how to do it for all of them in one go. But I'm unsure of how to do that.