MySql中的条件更新 [英] Conditional UPDATE in MySql

问题描述

我正在尝试更新表中的值，但是我需要添加一些条件.我找到了CASE函数，但是如果是最好的方法，我就不知道了. 这是一个示例:

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:

我的表是例如关系"

userid1|userid2|name1|name2

例如:

SELECT * 
  FROM realtion 
 WHERE (userid1 = 3 AND userid2 = 4)
    OR (userid1 = 4 AND userid2 = 3);  

输出:

     
4|3|bill|jack

我想在3和4之间的关系中更改用户3的名称，但我不知道它是userid1还是userid2.

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.

尽管如此

UPDATE relation 
   CASE WHEN userid1 = 3 THEN SET name1 = 'jack' END 
        WHEN userid2 = 3 THEN SET name2 = 'jack' 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.

推荐答案

不幸的是，最终要获得的结果还不是很清楚，但这是您可以在UPDATE中正确使用条件SET的方法.

Unfortunately it's not very clear what you want to get in the end, but here is how you could correctly use conditional SET in your UPDATE

UPDATE relation 
   SET name1 = CASE WHEN userid1 = 3 THEN 'jack' ELSE name1 END,
       name2 = CASE WHEN userid2 = 3 THEN 'jack' ELSE name2 END
WHERE (userid1 = 3 AND userid2 = 4) 
   OR (userid1 = 4 AND userid2 = 3);

这里是 SQLFiddle 演示.

Here is SQLFiddle demo.

这篇关于MySql中的条件更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！