Updating a MySQL Field Based on its Row’s Position in a Select

Let’s say you have the following data in a table:

name	position
Stevie Ray Vaughan	1
Derek Trucks	3
Joe Bonamassa	2

Now, what if we wanted to update the position value of each row to match the order if we were to sort by name…

Here’s what the desired table would look like:

name	position
Stevie Ray Vaughan	3
Derek Trucks	1
Joe Bonamassa	2

To figure this out, the first thing I did was write the SELECT query I needed to target only the fields I wanted to update. There is lots of other data in the table (it’s a table of tasks, milestones, and discussions) and I just wanted to change the position value of some specific subset of this data (tasks that are not completed).

Here’s what the select query looked like:

1	SELECT name, position FROM 18314_project_objects WHERE project_id = 349 AND completed_on IS NULL AND state = 3 AND type = "Task" ORDER BY name

Now I needed to know the position of each row in the result, so I worked out this query (thanks to Xgc in #mysql for pointing me to @variable). Test UPDATE queries on a cloned/test table!

1 2	SET @i = 0; SELECT name, position, @i:=@i+1 as rank FROM 18314_project_objects WHERE project_id = 349 AND completed_on IS NULL AND state = 3 AND type = "Task" ORDER BY name

That resulted in a dataset that looked like this:

name	position	rank
Stevie Ray Vaughan	1	1
Derek Trucks	3	2
Joe Bonamassa	2	3

The last step was to come up with the UPDATE query necessary to change the position value based on the rank.

1 2	SET @i = 0; UPDATE 18314_project_objects SET position = (@i:=@i+1) WHERE project_id = 349 AND completed_on IS NULL AND state = 3 AND TYPE = "Task" ORDER BY name

That’s it!

After I worked all of this out I got another message from Xgc, who provided me with an alternate (and probably safer) solution, shown here: