SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2009
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Simple question about UPDATE in PMA

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

    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2009
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Dada_Dan View Post
    I want to do a mass UPDATE of that data...
    what is it that you want to update?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jan 2009
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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\nattrib2=1\n etc would do it.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    UPDATE daTable
       SET attribs = REPLACE(attribs,'attrib2=0','attrib2=1')
     WHERE attribs LIKE '%attrib2=0%'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •