0

This sounds like a basic stuff but I'm running our of caffeine and my brain is seizing...

I have a table with where couple columns are set as datatype = text and contain comma separated strings, something like this

id   |   labels                       | items
------------------------------------------------
123  |  Resources,Faculty Resources   | 2323,97

If I know row ID can I append new values to labels and items via UPDATE or do I first need to query the table, get data, append values and then do update with new string?

GMB
  • 216,147
  • 25
  • 84
  • 135
santa
  • 12,234
  • 49
  • 155
  • 255

1 Answers1

1

A single update query can do:

update mytable 
set items = concat(coalesce(concat(items, ','), ''), :new_item)
where id = :id

... where :new_item is the item you want to add and :id is the id of the target row.

The coalesce()/concat() logic takes in account the possibility that items could be null on an existing row. If that's never the case, then simply:

update mytable 
set items = concat(items, ',', :new_item)
where id = :id

Note, however, that storing delimited lists in a table is bad practice, and should be generally avoided. More about this can be read in this famous SO question.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Brilliant! Checked the list about storing a string - none applies in this case, but definitely good to know. Thanks for sharing it. – santa Jul 23 '20 at 15:50
  • @santa: it probably applies somehow... At least, you could be using a JSON array, which MySQL supports since version 5.7, and that is *a little* easier to manipulate than a string. – GMB Jul 23 '20 at 15:54
  • Hmm, good point. I'll put on my list for next version. This is not my code and is pretty old. Unfortunately I don't have time to go though all codebase and fix it now. Cheers. – santa Jul 23 '20 at 15:57