MySQL Update multiple rows in one column based on values ​​from the same column

Question

I have a table that looks like this:

ID Key Value Order 1 gender m 0 2 gender f 0 34 age 10 0 35 age 80 0 

To update these lines, I should use the following:

 UPDATE `DemoGroup` SET `value` = 'male' WHERE `value` = 'm' UPDATE `DemoGroup` SET `value` = 'female' WHERE `value` = 'f' UPDATE `DemoGroup` SET `value` = '10-19' WHERE `value` = '10' UPDATE `DemoGroup` SET `value` = '80-89' WHERE `value` = '80' 

Is there a way to combine this into a single update statement without using an identifier (which is not guaranteed to be the same), for example (although this will not work) ...

 UPDATE `DemoGroup` SET `value`= CASE `value` WHEN 'm' THEN 'male', WHEN 'f' THEN 'female' END WHERE `value` = 'm' OR `value` = 'f' 

Even more bonus (but not necessary) if I could figure out how to set the Order field also for each row ...

Answer 1

You should probably update values ​​based not only on the value value , but also on the key value, otherwise you could update the "m" to "male" when the key is "shirt size".

 UPDATE `DemoGroup` SET `value` = CASE WHEN (`key`, `value`) = ('gender', 'm') THEN 'male' WHEN (`key`, `value`) = ('gender', 'f') THEN 'female' WHEN (`key`, `value`) = ('age', '10') THEN '10-19' WHEN (`key`, `value`) = ('age', '80') THEN '80-89' ELSE `value` -- no-op for other values END WHERE `key` IN ('gender','age'); 

Answer 2

MySQL has a case function. Try:

 UPDATE DemoGroup SET `value` = CASE `value` WHEN 'm' THEN 'male' WHEN 'f' THEN 'female' WHEN '10' THEN '10-19' WHEN '80' THEN '80-89' END;