Query SQL Update from select

Question

How to update this column rank with a SQL query?

char_id|char_name|point|w_point|rank|
-------|---------|-----|-------|----|
 100000|Adminn   |   17|     17|   0|
 100008|admin3   |    2|      2|   0|

with select

SELECT 
    char_id, 
    char_name, 
    `point`,
    `w_point`,
    RANK() OVER (ORDER BY `point` DESC) as `rank`
FROM 
    glro_worlddrop;

The result I want in column rank update

char_id|char_name|point|w_point|rank|
-------|---------|-----|-------|----|
 100000|Adminn   |   17|     17|   1|
 100008|admin3   |    2|      2|   2|

Thanks

Answer 1

Assuming char_id is unique, use JOIN:

UPDATE glro_worlddrop w JOIN
       (SELECT w.*,
               RANK() OVER (ORDER BY `point` DESC) as new_rank
        FROM glro_worlddrop w
       ) ww
       USING (char_id)
    SET w.rank = ww.new_rank;