MySQL UPDATE将数据附加到列中

时间:2022-08-16 16:28:36

I need to UPDATE tablename (col1name)

我需要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'

如果已有数据,我需要用值'a,b,c'附加它如果它是NULL,我需要添加值'a,b,c'

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

我知道有一个CONCAT参数,但不确定SQL语法是什么。

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

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

Is the above correct?

以上是正确的吗?

4 个解决方案

#1


48  

Try this Query:

试试这个查询:

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

Refer this sql fiddle demo.

请参阅此sql小提琴演示。

#2


5  

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

#3


4  

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

请参阅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 |

#4


0  

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

IFNULL(列,'')保存任何if语句,使SQL更简单!

MySQL 5.6 Schema Setup:

MySQL 5.6架构设置:

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 |

#1


48  

Try this Query:

试试这个查询:

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

Refer this sql fiddle demo.

请参阅此sql小提琴演示。

#2


5  

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

#3


4  

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

请参阅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 |

#4


0  

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

IFNULL(列,'')保存任何if语句,使SQL更简单!

MySQL 5.6 Schema Setup:

MySQL 5.6架构设置:

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 |