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?
Try this Query:
update tablename set col1name = concat(ifnull(col1name,""), 'a,b,c');
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');
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
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 |
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' )
;
select *
from tablename
| yourcol |
|-----------------|
| sadsdhsomevalue |
| somevalue |
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.
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')
