MySQL updating a field with comma if a value already exists-CodePudding

How can I execute an update query on a field in a table by checking in the query itself that if the field is empty or not. If it's empty then perform a normal update else update by adding a comma with the new value.

For example:

Say in table users I have a field named genders.

Case 1: The field is empty for the chosen row. Update value male.

Case 2: The field is not empty and has a value (for example, male). Update with ,female. Now the field value becomes male,female.

Case 3: If field is not empty and has a value then keep a check to not repeat the update. For example, field has male,female and update query is run again then the it will update male,female,female as per CASE 2. This should be avoided.

I want to perform this within the query itself. Any idea on how to do this?

CodePudding user response：

Get an idea from following and modify the query according to your requirement.

UPDATE users SET genders = CASE
    WHEN genders = '' OR genders is null THEN 'male'
    WHEN genders ='male' THEN CONCAT(genders,',','female') 
    WHEN genders ='female' THEN CONCAT(genders,',','male') 
    ELSE genders
    END

CodePudding user response：

Use CONCAT_WS function. It skips nulls and use just the right amount of commas:

SELECT CONCAT_WS(',', null, 'aa') -- aa
SELECT CONCAT_WS(',', 'aa', null) -- aa
SELECT CONCAT_WS(',', 'aa', 'bb') -- aa,bb