select version();

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;

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

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;