MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table test (
  id int,
  name varchar(25),
  gold int,
  silver int,
  bronze int,
  disq int,
  penalty int,
  `rank` int
);

insert into test values
(1,'ben',1,4,8,null,5,null),
(2,'kim',4,1,3,1,2,null),
(3,'sarah',2,2,1,null,null,null),
(4,'matt',null,10,2,1,3,null),
(5,'jane',2,3,5,null,1,null);

Records: 5  Duplicates: 0  Warnings: 0


select * from test;


id
name
gold
silver
bronze
disq
penalty
rank


1
ben
1
4
8
null
5
null

2
kim
4
1
3
1
2
null

3
sarah
2
2
1
null
null
null

4
matt
null
10
2
1
3
null

5
jane
2
3
5
null
1
null


    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


id
rank
new_rank


4
37
1

5
37
1

2
36
3

1
35
4

3
26
5


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`;

Rows matched: 5  Changed: 5  Warnings: 0


select * 
from test
order by `rank` ;


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

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