Check Contents of Field before using Concat in MySQL Update Statement

Question

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!

aF. · Accepted Answer · 2011-12-19 17:45:47Z

1

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.

edited Dec 19, 2011 at 17:45

answered Dec 19, 2011 at 17:18

aF.

67k45 gold badges141 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jamie Hartnoll Over a year ago

Thanks @aF That looks perfect, but I'm getting Syntax errors. Trying to update several fields at once

"UPDATE tableName SET mobile='" & mobile & "', invoiceList = case when len(invoiceList) > 1 then concat(invoiceList, '," & currentInvoiceNumber & "') else '" & currentInvoiceNumber & "' end

Is the relevant snippet. I keep getting "Check the syntax near len(in "

Collectives™ on Stack Overflow

Check Contents of Field before using Concat in MySQL Update Statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related