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

User · Answer

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

User · Answer

UPDATE mytable SET spares   CONCAT spares        818   WHERE id   1   not working for me   spares is NULL by default but its varchar

User · Answer

That s pretty much all you need   mysql gt  select   from t   ------ -------    id     data     ------ -------       1   max          2   linda        3   sam          4   henry    ------ -------  4 rows in set  0 02 sec   mysql gt  update t set data concat data   a    Query OK  4 rows affected  0 01 sec  Rows matched  4  Changed  4  Warnings  0  mysql gt  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

User · Answer

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

User · Answer

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

User · Answer

CONCAT with a null value returns null  so the easiest solution is      UPDATE myTable SET spares   IFNULL  CONCAT  spares    string      string

User · Answer

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

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

Examples related to sql

Examples related to mysql

Examples related to concatenation

Examples related to concat