table update

add to existing field

[sssweb]

What's the SQL for updating a table when I want to add a string to the existing field?

So if db_table.field = 'old text', and I want to add 'new text', after the update field should = 'old text new text'

I tried the following, but got a 1064 error:

UPDATE db_table SET field = field + 'new text' WHERE field = 'old text';

[LifeinAsia]

It depends on your DB. Try & instead of +.

[sssweb]

Thanks for your response.

I'm using MySQL -- '&' didn't work.

[LifeinAsia]

Did a quick Google- try
UPDATE db_table SET field = CONCAT(field,'new text') WHERE field = 'old text';

[sssweb]

ahhh...the ol' CONCAT -- thanks, works like a charm!

[coopster]

ANSI Standard SQL uses the concatenation operator (do not forget that the forum breaks the pipe symbol):

UPDATE db_table SET field = field ¦¦ ' ' ¦¦ 'new text' WHERE field = 'old text';

But if you are using MySQL you may find that it behaves more like the logical

OR

as in the C programming language. One of my pet peeves with MySQL's extensions to the standard [dev.mysql.com].

MySQL Server understands the ¦¦ and && operators to mean logical OR and AND, as in the C programming language. In MySQL Server, ¦¦ and OR are synonyms, as are && and AND. Because of this nice syntax, MySQL Server doesn't support the standard SQL ¦¦ operator for string concatenation; use CONCAT() instead. Because CONCAT() takes any number of arguments, it's easy to convert use of the ¦¦ operator to MySQL Server.

A related discussion that has a little detail in regards to what may happen if you use them in your code, such as PHP:
[webmasterworld.com...]