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.

Petcock

Have you actually tried your query? It should "just work" – Dekeles

Yes I have tried it. I thought it should just work too. – Petcock

here is my 'real life' return: [SQL] UPDATE questions_national SET cat_id = CONCAT(cat_id,'a') Affected rows: 0 Time: 0.069ms – Petcock

Is cat_id a character field (varchar, text, etc) or numeric? – Dekeles

was not working for me (SQL 2012)so I tried "update t set data=data+'a'" works fine.. – Bolo

Answer 1

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

Enfilade

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

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

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? – Savino

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

Answer 2

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

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

Ernaline

Perfect solution. – Endomorph

Answer 3

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

Catamount

Answer 4

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.

Bolduc

Answer 5

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

not working for me.

spares is NULL by default but its varchar

Mounts

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

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. – Trask

Answer 6

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

Petcock

Answer 7

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.

Jadwigajae

Can you really use + with strings in mysql ? – Eutectoid

Answer 8

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

Graniela