Simple question about UPDATE in PMA

Databases
1

So I am looking at a column in PMA - it’s a multiline field that for a single row has data like:


attrib1=0
attrib2=1
attrib3=1

Each key value pair is on a separate line.

I want to do a mass UPDATE of that data, but what method can I use to ensure the replacement data is similarly on separate lines? If I do UPDATE table set attribs = "attrib1=0 is fall over as I need a line break after =0…

Thanks!

2

you have multiple “lines” of key/value pairs in a single table column value?

may i humbly suggest that you consider changing this data model

3

I’d love to, but it’s baked in to the CMS and I’m loathe to trawl the thing looking for all instances where the data is pulled out and used :frowning:

4

what is it that you want to update?

5

Thanks Rudy.

I actually only want to update one of the KVP’s - in this case say attrib2 - from attrib2=0 to attrib2-1.

I thought able just writing a one-off php page to do it, but am not sure attrib1=0
attrib2=1
etc would do it.

6 
UPDATE daTable
   SET attribs = REPLACE(attribs,'attrib2=0','attrib2=1')
 WHERE attribs LIKE '%attrib2=0%'