UPDATE profile SET favourties=CONCAT(favourties,"123") WHERE id=1
i want to append 123 in favourties but if default value of favourties is set to NULL then this query will not work. What will be query if favourties is set to NULL and then append 123 with it
UPDATE profile SET favourties=CONCAT(IFNULL(favourties, ''),"123") WHERE id=1
Wrap the field around with the COALESCE function:
UPDATE profile
SET favourties = CONCAT(COALESCE(favourties, ''),"123")
WHERE id=1
You probably can't concatenate something to 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 like @zerkms says, but that is not in the SQL standard. It is a tiny tad faster though. Read up on it at this link: http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx
You can also use CONCAT_WS() (docs) which deals with NULLs as you would expect: converts them to an empty string:
UPDATE profile SET favourties = CONCAT_WS('', favourties, "123") WHERE id = 1;
I personally actually only use CONCAT_WS() now because having to worry about NULLs annoys me. I rarely use NULLs so I don't have to worry about it, but just incase. It's just an annoying thing to figure out why you you're ending up with an empty string when it just doesn't seem to make sense.
In PHP, I use:
SET `trans`=concat('$var', trans)
to add to a string already in the trans column.
It wouldn't work on column named group without using back ticked group inside the brackets as well, whereas with trans, back ticks were not needed.
