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.

https://stackoverflow.com/a/35082542/3063226 – Heitor Jun 15 '20 at 05:10 — Heitor, Jun 15 '20 at 05:10

peterm · Accepted Answer · 2013-06-23T21:44:14.630

76

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.

edited Jun 23 '13 at 21:44

answered Jun 23 '13 at 21:36

peterm

91,357
15
148
157

Thanks much for your help, I am testing it right now, I keep you in touch. PS: I saw your formating improvement I will keep this in mind for further post – Pierre Lebon Jun 23 '13 at 21:55
@peterm I am also looking at this problem and this method cannot prevent the increment of the affectedRows in the returned result which I want to use to confirm if its a success update or failure update due to outdated values. – Nick May 30 '16 at 11:00
https://stackoverflow.com/a/35082542/3063226 is much better solution! – Heitor Jun 15 '20 at 05:10

score 9 · Answer 2 · answered Feb 20 '21 at 07:47

I know I'm a little late to this, but I think an easier to read solution would be to use the IF() function in MySQL:

UPDATE relation
    SET name1 = IF(userid1 = 3, 'jack', name1),
        name2 = IF(userid2 = 3, 'jack', name2)
WHERE (userid1 = 3 AND userid2 = 4) 
   OR (userid1 = 4 AND userid2 = 3);

The IF function is used like this:

IF(condition, value_if_true, value_if_false)

Conditional UPDATE in MySQL

2 Answers2

Linked