CODEWITHSUNDEEP
mysqlsqlconcatenation
Jamie Hartnoll
Jamie Hartnoll

Reputation: 7361

Check Contents of Field before using Concat in MySQL Update Statement

I am updating fields in a database using Concat as follows

`fieldName`=concat( `FieldName`, ',NEW DATA')

In some instances though, I am trying to create a comma separated list in the field, such as

item 1, item 2, item 3

But unless I know whether the field is empty before hand, if this is the first entry in the field I cannot avoid having a preceeding, or trailing comma.

,item 1, item 2...etc

or 

item 1, item 2, ... etc

Is there a way I can determine if the field has a content before I do add data, within the Update statement to avoid having the query the database separately?

... or a better way of making a comma separated list!

Upvotes: 2

Views: 429

Answers (1)

aF.
aF.

Reputation: 66757

Yes, use a case expression like this:

update tableName
set columnName = 
  case when LENGTH(columnName) > 1
    then select concat(columnName, ',NEW DATA')
    else select 'NEW DATA' end

If the length of the columnName is bigger then 1, there is data in the columName so you can put the comma, otherwise you only put the new data.

Upvotes: 1

Related Questions