Brijesh kumar Brijesh kumar - 3 months ago 9
MySQL Question

How to update two attributes in mysql depending on other attibute using CASE

How can I update two columns in MySQL depending on a value of another column ?

This is my query so far:

UPDATE conversation
SET CASE WHEN user_id = 100
THEN user_status='true'
WHEN pro_user_id=100
THEN pro_user_status='true'
ELSE NULL
END
WHERE conv_id=12


I'm using php progamming language.

Answer

You can't conditionally decide which column to update, so just update the column if it meets the criteria , if not , keep the value it had before :

UPDATE conversation 
SET user_status = CASE WHEN user_id = 100
                       THEN 'true'
                       ELSE user_status
                  END,
    pro_user_status = CASE WHEN pro_user_id = 100 
                           THEN 'true'
                           ELSE pro_user_status
                      END
WHERE conv_id=12 
Comments