Mysql add (not replace) a string to a column in multiple rows

PHP
1

I know that I can replace the content of multiple rows with this code:

UPDATE mytable
SET table_column = 'test';

But I don’t want replace the possibly existing data of table_column, I just want add the new string ‘test’ within table_column.
How should do?

2

Your question is typical of a bad database design, of storing multiple values in a single column.

What it is the top level overall goal you are trying to accomplish?

2 Likes
3

Maybe my words were not appropriate (there was also a typing, now fixed, mistake: date instead of data): sorry. I try to explain better my question:

My keyword column has several rows, i.g.:

'red'  
'NULL'  
'red, yellow'  
'green'  
'NULL'  
'yellow, green'

My aim is to add a new string (‘blue’) to every row, so that the result be:


'red, blue'  
'blue'  
'red, yellow, blue'  
'green, blue'  
'blue'  
'yellow, green, blue'

I hope in this way it be clearer what I mean.
Thank you.

4

I have found:

UPDATE my_table  
SET mycolumn = CONCAT('same new string ',mycolumn);

This code works!

EDIT

It works however only for rows with some content. But not for the empty (NULL) ones.

EDIT

This code worked for the NULL column:

UPDATE my_table
SET mycolumn = 'same new string'
WHERE mycolumn IS NULL;
5

As already mentioned your database design is bad.
I guess each value in the column stands for one clearly defined setting.
So why not create a correct datatable like this

CREATE TABLE settings
(
    user_id INT,
    key VARCHAR(20),
    value VARCHAR(200),
    PRIMARY KEY idx (user_id, key)
)

Key then should be the setting name.
User_id is only needed if you have multiple user of course

In that Case you can easily filter by settings for a user, you can even group_concat the values to one string if really needed (what I don’t think so)
You can even directly create a settings object with one query.

At the end I always go with JSON columns for such data storage

1 Like
6

Sorry, but I don’t understand at all what you say. I guess that I use the word value with a different meaning that yours.
For my needs the problem I had is now fixed…

7

If you don’t fix your database design now your problems are just beginning. Do not store comma separated data in a column. Learn about Database Normalization.

2 Likes
8

If you don’t want to learn we can’t help, at the end it’s your decision. I wish you much luck you will need it…

9

I will see. In another thread I am going to learn about this.
Thank you!

10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.