Kranthi Kumar Gurrala Kranthi Kumar Gurrala - 1 year ago 77
MySQL Question

MySQL update query with 'concat()' value based on a 'CASE' not working

I am trying to update the a column value by appending a string to itself based on if the column value doesn't already have it. I thought I had it right but am unable to get it to work. Can someone help me understand if I am heading in a wrong direction. I would also appreciate if you could show me if there is a better way of doing this.



$additional_condition = 'string with special chars and quotes';

update my_table set my_column = concat(my_column, CASE WHEN my_column LIKE '%$additional_condition%' THEN $additional_condition ELSE '' END) where my_name in ('x', 'y');

Answer Source

If I understand correctly, you should move the condition to the WHERE clause:

update my_table
    set my_column = concat(my_column, $additional_condition)
    where my_name in ('x', 'y') and
          my_column not like '%$additional_condition%';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download