SQL UPDATE all values in a field with appended string CONCAT not working

Question

Here is what I want to do:

current table:

+----+-------------+  
| id | data        |  
+----+-------------+  
|  1 | max         |  
|  2 | linda       |  
|  3 | sam         |  
|  4 | henry       |  
+----+-------------+

Mystery Query ( something like "UPDATE table SET data = CONCAT(data, 'a')" )

resulting table:

+----+-------------+  
| id | data        |  
+----+-------------+  
|  1 | maxa        |  
|  2 | lindaa      |  
|  3 | sama        |  
|  4 | henrya      |  
+----+-------------+

thats it! I just need to do it in a single query, but can't seem to find a way. I am using mySQL on bluehost (I think its version 4.1)

Thanks everyone.

here is my 'real life' return: [SQL] UPDATE questions_national SET cat_id = CONCAT(cat_id,'a') Affected rows: 0 Time: 0.069ms — Fresheyeball
– Fresheyeball, Commented Nov 8, 2010 at 22:00
Is cat_id a character field (varchar, text, etc) or numeric? — Phil
– Phil, Commented Nov 8, 2010 at 23:02
was not working for me (SQL 2012)so I tried "update t set data=data+'a'" works fine.. — Silver
– Silver, Commented Nov 29, 2016 at 7:46

Marc B · Accepted Answer · 2010-11-08 21:46:14Z

335

That's pretty much all you need:

mysql> select * from t;
+------+-------+
| id   | data  |
+------+-------+
|    1 | max   |
|    2 | linda |
|    3 | sam   |
|    4 | henry |
+------+-------+
4 rows in set (0.02 sec)

mysql> update t set data=concat(data, 'a');
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from t;
+------+--------+
| id   | data   |
+------+--------+
|    1 | maxa   |
|    2 | lindaa |
|    3 | sama   |
|    4 | henrya |
+------+--------+
4 rows in set (0.00 sec)

Not sure why you'd be having trouble, though I am testing this on 5.1.41

answered Nov 8, 2010 at 21:46

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Phil Over a year ago

The concat function in 4.1 looks the same - dev.mysql.com/doc/refman/4.1/en/…

Fresheyeball Over a year ago

Solved it. Turns out the column had a limited set of characters it would accept, changed it, and now the query works fine.

Shaonline Over a year ago

I have somewhat the same senario, except i want to replace all the double quotes with single quotes. Any suggestions how can i do that?

Kiky Rodriguez Over a year ago

This was a good answer, but a little confusing, since on my server 'data' was a keyword. Maybe a less ambiguous example would be: UPDATE table SET column_name=concat(column_name, 'string');

andrejc · Accepted Answer · 2013-11-14 11:38:10Z

49

CONCAT with a null value returns null, so the easiest solution is:

UPDATE myTable SET spares = IFNULL (CONCAT( spares , "string" ), "string")

edited Nov 14, 2013 at 11:38

answered Nov 14, 2013 at 11:31

andrejc

6105 silver badges6 bronze badges

1 Comment

REMITH Over a year ago

Perfect solution.

Rohan Khude · Accepted Answer · 2015-11-04 10:02:13Z

20

convert the NULL values with empty string by wrapping it in COALESCE

"UPDATE table SET data = CONCAT(COALESCE(`data`,''), 'a')"

OR

Use CONCAT_WS instead:

"UPDATE table SET data = CONCAT_WS(',',data, 'a')"

answered Nov 4, 2015 at 10:02

Rohan Khude

5,0015 gold badges52 silver badges48 bronze badges

Comments

andrewsi · Accepted Answer · 2012-09-21 16:49:49Z

17

UPDATE 
    myTable
SET 
    col = CONCAT( col , "string" )

Could not work it out. The request syntax was correct, but "0 line affected" when executed.

The solution was :

UPDATE 
    myTable 
SET 
    col = CONCAT( myTable.col , "string" )

That one worked.

edited Sep 21, 2012 at 16:49

andrewsi

10.7k132 gold badges37 silver badges52 bronze badges

answered Sep 21, 2012 at 10:55

Jeremy Thille

26.4k12 gold badges48 silver badges64 bronze badges

Comments

coolguy · Accepted Answer · 2012-10-29 08:41:57Z

13

UPDATE mytable SET spares = CONCAT(spares, ',', '818') WHERE id = 1

not working for me.

spares is NULL by default but its varchar

edited Oct 29, 2012 at 8:41

coolguy

7,9549 gold badges48 silver badges72 bronze badges

answered Sep 23, 2012 at 10:12

Demiro-FE-Architect

3,74114 gold badges56 silver badges90 bronze badges

2 Comments

Demiro-FE-Architect Over a year ago

it seems, that if the value is NULL by default it doesnt work. it has to be an empty string

pbarney Over a year ago

UPDATE mytable SET spares = CONCAT(COALESCE(spares, ''), ',', '818') WHERE id = 1 This will work. It uses a blank string '' when it runs into null values.

Fresheyeball · Accepted Answer · 2010-11-09 03:20:09Z

9

Solved it. Turns out the column had a limited set of characters it would accept, changed it, and now the query works fine.

answered Nov 9, 2010 at 3:20

Fresheyeball

30.1k21 gold badges106 silver badges168 bronze badges

Comments

Eric · Accepted Answer · 2013-05-24 18:19:23Z

4

You can do this:

Update myTable
SET spares = (SELECT CASE WHEN spares IS NULL THEN '' ELSE spares END AS spares WHERE id = 1) + 'some text'
WHERE id = 1

field = field + value does not work when field is null.

answered May 24, 2013 at 18:19

Eric

70511 silver badges27 bronze badges

1 Comment

Sudhir N Over a year ago

Can you really use + with strings in mysql ?

Vishal Pathriya · Accepted Answer · 2023-11-16 12:37:53Z

0

UPDATE table_name SET column_name = CONCAT('text', column_name)

answered Nov 16, 2023 at 12:37

Vishal Pathriya

854 bronze badges

Comments

oldpride · Accepted Answer · 2024-11-26 12:03:48Z

-1

this works in 2024 in Microsoft SQL server.

UPDATE table SET data = data + 'a'

The syntax is likely to be in place for many years already.

answered Nov 26, 2024 at 12:03

oldpride

1,06312 silver badges27 bronze badges

Collectives™ on Stack Overflow

SQL UPDATE all values in a field with appended string CONCAT not working

9 Answers 9

4 Comments

1 Comment

Comments

Comments

2 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

4 Comments

1 Comment

Comments

Comments

2 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related