Convert Selection query to update query

user_10878245 0
Viewed 86

I am trying to select rank in sql, the selection query working fine how to convert this selection into update and update the columns as well.

SET @i=0  ;

SELECT sno, email, points, @i:=@i+1 AS rank FROM user ORDER BY points DESC

how to update this selection in table as well

Got the query from here

mysql sql
2 Answers
SET @i=0; 
UPDATE user SET rank= @i:= (@i+1) ORDER BY points DESC;
user_103896160

Assuming that sno is the primary key of your table, you want :

SET @i=0  ;
UPDATE user
JOIN (
    SELECT sno, email, points, @i:=@i+1 AS rank
    FROM user 
    ORDER BY points DESC
) AS ranks ON ranks.sno = user.sno 
SET user.rank = ranks.rank
user_106767160
Related