Mysql Query update multiple columns with multiple condition using WHEN, AND

Question

I'm writing sql UPDATE to 2 columns with 2 conditions. Before I run sql the sender_del_flag and receiver_del_flag are all 0 but after I run this some value return null. I wonder why it changed to null. How to fix this?

   UPDATE `messages` SET 
        receiver_del_flag = CASE 
            WHEN `id`='4' AND receiver='92' THEN '1' 
            WHEN `id`='6' AND receiver='92' THEN '1' 
            WHEN `id`='8' AND receiver='92' THEN '1' 
        END, 
        sender_del_flag = CASE 
            WHEN `id`='4' AND sender='92' THEN '1' 
            WHEN `id`='6' AND sender='92' THEN '1' 
            WHEN `id`='8' AND sender='92' THEN '1' 
        END 
    WHERE id IN ('4', '6', '8')

enter image description here

Gordon Linoff · Accepted Answer · 2014-09-05 02:25:06Z

5

This fixes the problem:

   UPDATE `messages` SET 
        receiver_del_flag = CASE 
            WHEN `id`='4' AND receiver='92' THEN '1' 
            WHEN `id`='6' AND receiver='92' THEN '1' 
            WHEN `id`='8' AND receiver='92' THEN '1' 
            ELSE receiver_del_flag
        END, 
        sender_del_flag = CASE 
            WHEN `id`='4' AND sender='92' THEN '1' 
            WHEN `id`='6' AND sender='92' THEN '1' 
            WHEN `id`='8' AND sender='92' THEN '1' 
            ELSE sender_del_flag
        END 
    WHERE id IN ('4', '6', '8');

Your where clause is getting rows where the sender and receiver are 92. These are set correctly. It is also getting rows where the values are not 92. These are set to NULL, because there was no ELSE clause.

edited Sep 5, 2014 at 2:25

answered Sep 5, 2014 at 2:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Barmar Over a year ago

It will also be a more efficient if you add AND (receiver='92' OR sender='92') to the WHERE clause.

Khean Over a year ago

Ok i see but i think i don't have to add that because its already in WHEN clause

Gordon Linoff Over a year ago

@Khean . . . Barmar is saying that performance would be faster because on some rows, neither value is '92'. There is no reason to attempt to update those rows. In your sample data, this is rarely true, so this would be a minor optimization. However, if 92 were in only 1% of the rows, then the improvement might be significant.

Khean Over a year ago

I see. But the result may come out differently. If you know another query that give the same result but better performance please post as an answer.

Khean Over a year ago

Oh sorry now i understand. If we don't add what @Barmar said it will go through all records as result it will be slow . i will modify my code now thanks you everyone

Collectives™ on Stack Overflow

Mysql Query update multiple columns with multiple condition using WHEN, AND

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related