MySQL 8.0

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

select version();


version()


8.0.18


CREATE TABLE states_risk
    (`id` int, `state` varchar(2), `municipally` varchar(5), `rating` varchar(3))
;
INSERT INTO states_risk
    (`id`, `state`, `municipally`, `rating`)
VALUES
    (1, 'AG', 'AG1', '5'),
    (2, 'AG', 'AG2', '6'),
    (3, 'AG', 'AG3', '2'),
    (4, 'AG', 'AG4', '1'),
    (5, 'AG', 'OTHER', '-'),
    (6, 'AB', 'AB1', '0.2'),
    (7, 'AB', 'AB2', '2'),
    (8, 'AB', 'AB3', '10'),
    (9, 'AB', 'OTHER', '-')
;
Select * From states_risk;


id
state
municipally
rating


1
AG
AG1
5

2
AG
AG2
6

3
AG
AG3
2

4
AG
AG4
1

5
AG
OTHER
-

6
AB
AB1
0.2

7
AB
AB2
2

8
AB
AB3
10

9
AB
OTHER
-


SELECT state, MAX(CAST(rating as FLOAT)) MAXrating 
                              FROM states_risk WHERE rating <> '-' GROUP BY state


state
MAXrating


AG
6

AB
10


UPDATE states_risk sr INNER JOIN (SELECT state, MAX(CAST(rating as FLOAT)) MAXrating 
                              FROM states_risk WHERE rating <> '-' GROUP BY state) t1 
                              ON sr.state = t1.state
SET sr.rating = t1.MAXrating WHERE sr.municipally = 'OTHER';
Select * From states_risk;


id
state
municipally
rating


1
AG
AG1
5

2
AG
AG2
6

3
AG
AG3
2

4
AG
AG4
1

5
AG
OTHER
6

6
AB
AB1
0.2

7
AB
AB2
2

8
AB
AB3
10

9
AB
OTHER
10

id	state	municipally	rating
1	AG	AG1	5
2	AG	AG2	6
3	AG	AG3	2
4	AG	AG4	1
5	AG	OTHER	-
6	AB	AB1	0.2
7	AB	AB2	2
8	AB	AB3	10
9	AB	OTHER	-