<!-- --> > select version(); > > <pre> > | version() | > | :-------- | > | 8.0.18 | > </pre> <!-- --> > 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', '-') > ; > > <pre> > ✓ > > ✓ > </pre> <!-- --> > Select * From states_risk; > > <pre> > 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 | - > </pre> <!-- --> > SELECT state, MAX(CAST(rating as FLOAT)) MAXrating > FROM states_risk WHERE rating <> '-' GROUP BY state > > <pre> > state | MAXrating > :---- | --------: > AG | 6 > AB | 10 > </pre> <!-- --> > 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'; > > <pre> > ✓ > </pre> <!-- --> > Select * From states_risk; > > <pre> > 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 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f4bc3c4296e3c26de028f55f45cae343)*