MySQL CASE 更新多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13673890/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL CASE to update multiple columns
提问by Villermen
I would like to update multiple columns in my table using a case statement, but I cannot find how to do this (is this even possible). I came up with the following invalid reference query:
我想使用 case 语句更新表中的多个列，但我找不到如何执行此操作（这是否可能）。我想出了以下无效的参考查询：
UPDATE tablename SET
CASE name
WHEN 'name1' THEN col1=5,col2=''
WHEN 'name2' THEN col1=3,col2='whatever'
ELSE col1=0,col2=''
END;
Is there any way of achieving the expected result with valid SQL?
有没有办法用有效的 SQL 达到预期的结果？
回答by bobwienholt
UPDATE tablename
SET col1 = CASE WHEN name = 'name1' THEN 5
WHEN name = 'name2' THEN 3
ELSE 0
END
, col2 = CASE WHEN name = 'name1' THEN ''
WHEN name = 'name2' THEN 'whatever'
ELSE ''
END
;
回答by ean5533
I don't know of any clean way to do what you're asking. An equivalent valid SQL update would be:
我不知道有什么干净的方法可以做你所要求的。等效的有效 SQL 更新将是：
UPDATE tablename SET
col1 = CASE name WHEN 'name1' THEN 5 WHEN 'name2' THEN 3 ELSE 0 END,
col2 = CASE name WHEN 'name1' THEN '' WHEN 'name2' THEN 'whatever' ELSE '' END;
Of course this isn't pretty and requires repeating the same cases (e.g. 'name1') multiple times, but I just don't think it's possible any other way.
当然，这并不漂亮，需要'name1'多次重复相同的情况（例如），但我认为不可能有任何其他方式。
回答by shmosel
If namehas a unique index and your values are known to exist in the table, you can use this trick:
如果name有一个唯一索引并且已知您的值存在于表中，则可以使用以下技巧：
INSERT INTO tablename (name, col1, col2)
VALUES ('name1', 5, '')
, ('name2', 3, 'whatever')
ON DUPLICATE KEY UPDATE
col1 = VALUES(col1)
, col2 = VALUES(col2);
If there are any additional NOT NULLcolumns without a default value, you'll have to add dummy values for those. Just leave them out of the ON DUPLICATE KEY UPDATEand they'll be ignored.
如果有任何NOT NULL没有默认值的附加列，则必须为这些列添加虚拟值。只要将它们排除在外，ON DUPLICATE KEY UPDATE它们就会被忽略。
