add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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