Sql how to update a column while keeping the existing strings in the column

Question

I have a table in my database called Games. This table has a column called "players_attending" that stores varchars. I need to be able to add a new string into this column while still saving the previous strings.

For example, if one row of Games had players_attending = "bryan." I want to be able to add "matt" to players_attending but also still have bryan.

The result should be players_attending = "bryan matt."

Is this possible?

SQL uses `||` to concatenate strings: `players_attending = players_attending || 'matt'` — , Dec 07 '15 at 07:11

score 0 · Answer 1 · edited Dec 07 '15 at 07:19

0

Yes, you would concatenate the string using ||

update Games 
   set players_attending = players_attending || ' ' || new_player_name 
   where Id = x

edited Dec 07 '15 at 07:19

answered Dec 07 '15 at 07:15

Dan King

1,080
1
11
28

I tried that, by doing, UPDATE Games SET players_attending = players_attending ||'bryan625' WHERE location = 'Lady Bug Park' AND id = 1; and it just sets the value of players_attending to 0... – Bryan Penaloza Dec 07 '15 at 07:25
FYI I am using phpMyAdmin – Bryan Penaloza Dec 07 '15 at 07:26
What RDBMS are you using (Oracle, SQL server, MySQL, etc) – Dan King Dec 07 '15 at 07:28
@BryanPenaloza: In a default installation, MySQL uses `||` for something different. You should enable `PIPES_AS_CONCAT` in your installation to be more standard compliant. – Dec 07 '15 at 07:30

Muhammad Muazzam · Accepted Answer · 2015-12-07T07:32:33.780

0

Your query would be like:

Oracle:

Update table set field =  field || ' matt'

MySQL:

Update table SET col=CONCAT(col,' matt');

MSSQL:

Update table set col = col + ' matt'

edited Dec 07 '15 at 07:32

answered Dec 07 '15 at 07:17

Muhammad Muazzam

2,810
6
33
62

The first statement is not specific to Oracle. This is how string concatenation is defined in the SQL standard. And Postgres, DB2, Firebird, Ingres, Informix and many other DBMS comply with that standard (I think only MySQL and SQL Server chose to ignore that) – Dec 07 '15 at 07:28

score 0 · Answer 3 · answered Dec 07 '15 at 07:21

0

use concatenation to add two string and the where clause to get the required data records

update table GAMES set field = field || ' ' || 'Matt' where field = 'bryan'

answered Dec 07 '15 at 07:21

saikumarm

1,565
1
15
30

score 0 · Answer 4 · answered Dec 07 '15 at 07:21

0

You can update the column by concatenating value to it. SQL Server example

UPDATE YourTable
SET field = CAST(field AS VARCHAR(50)) + CAST('matt' AS VARCHAR(50))
WHERE (condition)

answered Dec 07 '15 at 07:21

Anil Namde

6,452
11
63
100

Sql how to update a column while keeping the existing strings in the column

4 Answers4