concat to text field

Question

I have a table with 1 column "textField". Our version of mysql won't allow for full text fields to have a default value, the default is null.

When we want to update our table we want to append a value to whatever is in textField, like so:

update table set textField = concat( textField ,'value')...this works when there is something already in the field, but won't work if the field is null. Since mysql won't allow for a default value in a full text field the above statement won't work.

A solution is to do 2 queries: 1. check if that field is null 2. if so, then don't do a concat & just update w/ the value...if not null then do the concat

We'd rather not do 2 queries if we can avoid it...is there a one-liner alternative that would work?

Answer 1

Try this:

update table set textField = concat(COALESCE(textField, '') ,'value')...

Answer 2

concat(ifnull(textfield, ''), 'value')