MySQL UPDATE append data into column

I need to UPDATE tablename (col1name)

If there is already data, I need to append it with values ‘a,b,c’
If it is NULL, I need to add the values ‘a,b,c’

I know there is a CONCAT argument, but not sure what the SQL syntax would be.

update tablename set col1name = concat(ifnull(col1name, 'a,b,c'), 'a,b,c')

Is the above correct?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Try this Query:

update tablename set col1name = concat(ifnull(col1name,""), 'a,b,c');

Refer this sql fiddle demo.

Method 2

This should do it:

update tablename set
col1name = if(col1name is null, 'a,b,c', concat(col1name, 'a,b,c'));

Or you could make your life easier by doing it in two steps:

update tablename set col1name = '' where col1name is null;

then

update tablename set col1name = concat(col1name, 'a,b,c');

Method 3

You can use the following:

update yourtable 
set yourcol = case when yourcol is null then 'a,b,c'
                  else concat(yourcol, ' a,b,c') end

See SQL Fiddle with Demo

Sample data:

CREATE TABLE yourtable(`yourcol` varchar(50));

INSERT INTO yourtable(`yourcol`)
VALUES  ('sadsdh'),
    (NULL);

Will return:

|      YOURCOL |
----------------
| sadsdh a,b,c |
|        a,b,c |

Method 4

IFNULL(column,”), saves any if statements, makes the SQL much simpler!

MySQL 5.6 Schema Setup:

CREATE TABLE tablename
    (`yourcol` varchar(50))
;

INSERT INTO tablename
    (`yourcol`)
VALUES
    ('sadsdh'),
    (NULL)
;

UPDATE tablename SET
    yourcol = CONCAT( IFNULL(yourcol,' '), 'somevalue' )
;

Query:

select *
from tablename

Results:

|         yourcol |
|-----------------|
| sadsdhsomevalue |
|       somevalue |

Method 5

why are you write ifnull function: it is obvious that if col1name1 is empty it concatenate to null means null+’a,b,c’ simply ‘a,b,c’
set col1name = concat(ifnull(col1name,””), ‘a,b,c’)
instead of this you can directly write
set col1name = concat(col1name, ‘a,b,c’)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0