MySQL UPDATE append data into column

Question

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?

Sunbonnet

@FahimParkar I didn't realize I could have used SQL Fiddle, but did not wish to try something uncertain on a live website. Thank you. – Sunbonnet

Answer 1

Try this Query:

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

Refer this sql fiddle demo.

Deanery

SQL fiddle demo outputs error for me (both in FF and Chrome). Created another one: sqlfiddle.com/#!9/8385a3/1/0 – Keelung

Answer 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');

Abohm

Answer 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 |

Hardcastle

Answer 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 |

Wootten

Answer 5

For those requiring a prepared-statement option, this example may help:

update tablename set col1name = concat(ifnull(col1name,""), ?) WHERE table_id = ?;

Regular binding will work; in this case two variables: col1name and table_id. This could be handy in change/upate logs. No need of grabbing previous logs.

Hauler

Answer 6

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')

Herisau