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:

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!

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.

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:

xgc_sqlfiddle

Posted in Uncategorized

Leave a Reply

Your email address will not be published. Required fields are marked *

*