I need assistance to write a single query that will spread the rank values for a given user between 1 (or 0) and a max value to leave space between values.
Given a table with id, rank, and user columns, I need help writing a single UPDATE query that will spread the rank values for a given user between 1 (or 0) and a max value, leaving space between values. For example, if the max value is 100 and the user is 1, the desired result would be:
id rank user
1 20 1
2 40 1
3 1 2
4 60 1
5 80 1
The query should be of the form:
UPDATE MyTable
SET rank = increment * ??? (?current position? in SELECT * FROM MyTable WHERE user = 1 ORDER BY rank)
WHERE user = 1