MySql 中的条件更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17265522/
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
Conditional UPDATE in MySql
提问by Pierre Lebon
I am trying to UPDATE values from a table but i need to add some conditions. I found the function CASE but i am not if it is the best method. Here is an example:
我正在尝试从表中更新值，但我需要添加一些条件。我找到了 CASE 函数，但如果它是最好的方法，我就不是。下面是一个例子：
My table is for example 'relation'
我的表是例如“关系”
userid1|userid2|name1|name2
I got for example:
我得到了例如：
SELECT *
FROM realtion
WHERE (userid1 = 3 AND userid2 = 4)
OR (userid1 = 4 AND userid2 = 3);
Output:
输出：
4|3|bill|Hyman
and i want to change the name of the user 3 in the relation between 3 and 4 but i don't know if it is the userid1 or the userid2.
我想在 3 和 4 之间的关系中更改用户 3 的名称，但我不知道它是 userid1 还是 userid2。
I though of case
我虽然如此
UPDATE relation
CASE WHEN userid1 = 3 THEN SET name1 = 'Hyman' END
WHEN userid2 = 3 THEN SET name2 = 'Hyman' END
WHERE (userid1 = 3 AND userid2 = 4)
OR (userid1 = 4 AND userid2 = 3);
But it doesn't work! Any ideas? Thanks very much in advance.
但它不起作用！有任何想法吗？首先十分感谢。
回答by peterm
Unfortunately it's not very clear what you want to get in the end, but here is how you could correctly use conditional SETin your UPDATE
可惜这不是很清楚你到底想要得到什么，但这里是你如何能正确的使用条件SET在UPDATE
UPDATE relation
SET name1 = CASE WHEN userid1 = 3 THEN 'Hyman' ELSE name1 END,
name2 = CASE WHEN userid2 = 3 THEN 'Hyman' ELSE name2 END
WHERE (userid1 = 3 AND userid2 = 4)
OR (userid1 = 4 AND userid2 = 3);
Here is SQLFiddledemo.
这是SQLFiddle演示。
