MySQL append string

Question

How can I append a string to the end of an existing table value? Let's say I have the table below:



And let's say that Maria Anders not only lived in Berlin but also Tokyo. How would I append the string
" Tokyo" onto the City column where ContactName = Maria Anders?

P.S. I want to be able to append on to a null value as well. Thus ending up with just the one city.

Solution 1

Use a combination of CONCAT and IFNULL (to handle the NULL case):

UPDATE `table`
   SET `City` = IFNULL(CONCAT(`City`, " Tokyo"), "Tokyo")
 WHERE `ContactName` = "Maria Anders";

---

However, appending seems wrong. If you want multiple City values (a many-to-many relationship), you should have a separate table for cities and a separate table for person-city relationships. Read about database normalisation.

Solution 2

You shouldn't have nested lists inside of a table. I would first create a table that associates each person with a unique ID. This is important in case you have someone else down the road with the same name.

ID  |  ContactName 
----+---------------
23  |  Maria Anders

Then have each location associated with an ID - For example:

ID  |  Address  |  City
----+-----------+------

Then when you need to add a new location for the same person, you can just add another value. For example:

ID  |  Address  |  City
----+-----------+------
23  | Foo ST.   | Baria
23  | Bar Ave.  | Fooia

Fetching the data and creating a list in your application/website will then be easy, as well as more flexible. Check out information on building one-to-many relationship tables via Google or the other places people suggested.