Brijesh kumar Brijesh kumar - 1 year ago 59
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'
WHERE conv_id=12

I'm using php progamming language.

Answer Source

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
    pro_user_status = CASE WHEN pro_user_id = 100 
                           THEN 'true'
                           ELSE pro_user_status
WHERE conv_id=12 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download