clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1135717 fiddles created (16611 in the last week).

select version();
version()
8.0.18
 hidden batch(es)


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', '-') ;
 hidden batch(es)


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 -
 hidden batch(es)


SELECT state, MAX(CAST(rating as FLOAT)) MAXrating FROM states_risk WHERE rating <> '-' GROUP BY state
state MAXrating
AG 6
AB 10
 hidden batch(es)


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';
 hidden batch(es)


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
 hidden batch(es)