MYSQL Concat not working

Question

UPDATE SET FAVORITES = CONCAT (favorites, "123") WHERE id = 1

I want to add 123 to favorites, but if the default value for favorites is set to NULL, this request will not work. What will the request be if favors is set to NULL and then add 123 with it

Answer 1

UPDATE profile SET favourties=CONCAT(IFNULL(favourties, ''),"123") WHERE id=1 

Answer 2

Wrap the box around with the COALESCE function:

 UPDATE profile SET favourties = CONCAT(COALESCE(favourties, ''),"123") WHERE id=1 

Answer 3

You probably can't associate something with NULL. Maybe you can use coalesce ?

 UPDATE profile SET favourties=CONCAT(COALESCE(favourites,""),"123") WHERE id=1 

see http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce

You can use ifnull as @zerkms says, but this is not in the SQL standard. However, this is a tiny step. Read it at this link: http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx

Answer 4

You can also use CONCAT_WS() ( docs ), which deals with NULL, as you would expect: converts them to an empty string:

 UPDATE profile SET favourties = CONCAT_WS('', favourties, "123") WHERE id = 1; 

I personally actually use CONCAT_WS() now, because worrying about NULL annoys me. I rarely use NULL, so I don’t have to worry about this, but just be discouraged. It is simply annoying to understand why you end up with an empty line when it just doesn't make sense.

Answer 5

In PHP, I use:

 SET `trans`=concat('$var', trans) 

to add to the row already in the trans column. It would not work with a column named group without using the back tick of group inside brackets, whereas with trans, back ticks are not needed.