mysql将同一列值改为一致,MySQL根据同一列中的值更新同一列上的多行

I have a table that looks like the following:

ID Key Value Order

1 gender m 0

2 gender f 0

34 age 10 0

35 age 80 0

To update these rows I have to 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 consolidate this into one update statement, without using the ID (which is not guaranteed to be the same), such as (even though this won't work)...

UPDATE `DemoGroup`

SET `value`= CASE `value`

WHEN 'm' THEN 'male',

WHEN 'f' THEN 'female' END

WHERE `value` = 'm' OR `value` = 'f'

Even more of a bonus (but not nessesary) is if I could figure out how to set the Order field as well for each row...

解决方案

You should probably update the values based not only on the value of value but on the value of key, otherwise you could update 'm' to 'male' when 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');