MySQL根据同一列中的值更新同一列上的多行 [英] MySQL Update multiple rows on a single column based on values from that same column

问题描述

我有一个如下表:

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'

是否有一种方法可以将其合并为一个更新语句，而无需使用ID(不能保证相同)，例如(即使此方法不起作用)...

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...

推荐答案

您可能不仅应基于value的值，而且还应基于key的值来更新值，否则可以将'​​m'更新为密钥为衬衫大小"时为男性".

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');

这篇关于MySQL根据同一列中的值更新同一列上的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！