Conditional UPDATE in MySQL

Question

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. My table is 'relation':

 userid1 | userid2 | name1 | name2 

I got for example:

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

Output:

 4 | 3 | bill | jack 

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.

I thought of case:

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.

Answer 1

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

Here is SQLFiddle demo.