I’m returning a set of records from the table ordering by the highest mark.
Select name,score from score_tbl order by score desc
Now what I want to do is on that score_tbl there is a column named position which I want to use an update statement to update that column based on the select query so the position is updated based user’s score
It sounds like you want to update the "position" value for all rows in your table based on the "score" field. In other words, the row with the highest score gets a position value of 1. Try this:
UPDATE score_tbl
FROM (
SELECT name, RANK() OVER(ORDER BY score DESC) ScorePosition
FROM score_tbl
) src
SET position = src.ScoreRank
WHERE name = src.name
This uses the "src" derived table where you generate the rankings to update your target table. This assumes "name" is the PK for your table.
Not sure what your DB is, so you will probably need to adjust the query a bit. But this should get you going. Let me know how it goes.
Updated
Try the query below:
UPDATE competitors
INNER JOIN (
SELECT
id,
RANK() OVER w AS 'ScorePosition'
FROM competitors
WINDOW w AS (ORDER BY score DESC)
) src ON competitors.id = src.id
SET position = src.ScorePosition;
It looks like windows functions were introduced in MySQL 8.0, so you'd need access to that version. I don't have access to this version, so I wasn't able to test. But let me know if it works.
Also, take a look at this post for some help:
