Having an issue with updating a rank column-CodePudding

My knowledge in MySQL is updating select deleting installing through PHP. I googled this and went to Stack overflow search terms were MySQL rank update. The ones I found that could help me. The persons that told them how to fix it. Told them do it through a select not an update, and even show them how to do it.

I know how to do it through a select Google taught me that.

I need to do it through an update.

Table and query are down below.

Table

region_id	pst	rank
101	20	1
101	19	2
101	19	2
101	15	4
100	20	1
100	13	2

Query

UPDATE `test1`
SET `rank` =
RANK() OVER(PARTITION BY `region_id` ORDER BY `pst` DESC)

When I run this code, I get this error. Window function is allowed only in SELECT list and ORDER BY clause.

Can you please show me how to fix this issue?

Thank you for your help in advanced

CodePudding user response：

You need a join of the table to a query that returns the ranks:

UPDATE test1 t1
INNER JOIN (
  SELECT region_id, pst, 
         RANK() OVER (PARTITION BY region_id ORDER BY pst DESC) `rank` 
  FROM test1
) t2 ON t2.region_id = t1.region_id AND t2.pst = t1.pst
SET t1.`rank` = t2.`rank`;

See the demo.