How can I update a column based on a calculation of other columns in a table?

I would like to give each player a rank / position based on the values of other columns, 1 being the best position. Each prize or penalty is worth a different amount. Can this be done in SQL and how would I go about putting this into a query? Here is the ratio that i had in mind:

((gold * 8) + (silver * 4) + (bronze * 2)) – ((disq * 4) + penalty) = points

The ‘points’ are insignificant, ideally I would be able to update the query if the ratio changes.

>Solution :

I would like to give each player a rank / position based on the values
of other columns, 1 being the best position

You could use the rank window function as follow:

select id, 
        rnk as `rank`, 
        rank()over( order by rnk desc) as new_rank
from ( select id,
              name,
              ((coalesce(gold,0) * 8) + (coalesce(silver,0) * 4) + (coalesce(bronze,0) * 2)) - ((coalesce(disq,0) * 4) + coalesce(penalty,0)) as rnk
       from test 
     ) as tbl;

https://dbfiddle.uk/3FiKkmVA

Note , you need to use coalesce for the null values

Or you could update your table with the rank

update test t
inner join ( select id, 
                    rnk as `rank`, 
                    rank()over( order by rnk desc) as new_rank
             from ( select id,
                           name,
                           ((coalesce(gold,0) * 8) + (coalesce(silver,0) * 4) + (coalesce(bronze,0) * 2)) - ((coalesce(disq,0) * 4) + coalesce(penalty,0)) as rnk
                    from test 
                   ) as tbl
            ) as t2 on t.id=t2.id
set t.`rank`=t2.`new_rank`;

Result:

 id    name    gold   silver    bronze    disq    penalty rank
 4     matt    null    10         2        1         3    1
 5     jane    2        3         5       null       1    1
 2     kim     4        1         3        1         2    3
 1     ben     1        4         8       null       5    4
 3     sarah   2        2         1      null       null  5

https://dbfiddle.uk/ckwNhf6y