SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot virginiamonkeys's Avatar
    Join Date
    Jun 2003
    Location
    Stafford, Virginia, USA
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How Do You Merge MySQL Columns?

    I have a MySQL database with street addresses in it. I found out today that I've been entering my addresses wrong. I need to merge as such:
    Table name: custbranch

    brname
    braddress1
    braddress2
    braddress3
    braddress4
    braddress5
    braddress6

    I have it set up like this so far:

    brname would be "John Smith"
    braddress1 would be "123 Main Street"
    braddress2 would be "Unit 45"
    braddress3 would be "Anytown"
    braddress4 would be "State"
    braddress5 would be "Zip"
    braddress6 would be "Country"

    I need to move braddress2 info to after braddress1 so braddress1 would read "123 Main Street, Unit 45" with the comma added between.

    I don't have braddress2 filled in for everybody, so I don't want commas that aren't needed, just for if there is info in braddress2 to move. Then I need braddress2 cleared and braddress3 moved to braddress2 spot. Then braddress3 cleared and info from braddress4 moved to braddress3. Then braddress4 cleared and info from braddress5 moved to braddress4. Then braddress5 cleared.

    I have 3 tables with this sort of user input error that I need to correct.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    caution: back up your data before testing this
    Code:
    UPDATE custbranch
       SET braddress1 = CONCAT_WS(',',braddress1,braddress2)
         , braddress2 = braddress3 
         , braddress3 = braddress4
         , braddress4 = braddress5
         , braddress5 = NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot virginiamonkeys's Avatar
    Join Date
    Jun 2003
    Location
    Stafford, Virginia, USA
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I didn't try this yet, but I can see a problem with this (I think).

    I don't want the separator in there (,) if the existing braddress2 is empty.


    And I don't want braddress6 NULLed, but I know how to work with the code to figure that one out. I just don't know what to do about the comma if the field is empty.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i guess you haven't played around with CONCAT_WS very much -- it works differently than CONCAT

    ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot virginiamonkeys's Avatar
    Join Date
    Jun 2003
    Location
    Stafford, Virginia, USA
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No I haven't.

    I just ran that command and now I have commas after all my addresses that didn't have a value previously in braddress2.

    I have 2 more tables to do. I don't want those commas where not needed. How do I do this?

  6. #6
    SitePoint Zealot virginiamonkeys's Avatar
    Join Date
    Jun 2003
    Location
    Stafford, Virginia, USA
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I could get this to work if I could trim the trailing ", ".

    I tried this command but got an error:

    UPDATE custbranch SELECT TRIM(
    TRAILING ', '
    FROM 'braddress1' )

  7. #7
    SitePoint Zealot virginiamonkeys's Avatar
    Join Date
    Jun 2003
    Location
    Stafford, Virginia, USA
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This works at trimming that trailing ", ".


    Code:
    UPDATE custbranch SET braddress1 = TRIM(TRAILING ', ' FROM braddress1)

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the reason you got the dangling comma is because you had an empty string in braddress2

    "not filled in" is usually reprsented by a NULL

    CONCAT_WS skips over NULLs in its parameters, but since the empty string isn't NULL, that's why you got the comma

    try this instead --
    Code:
    UPDATE custbranch
       SET braddress1 = CONCAT_WS(',',braddress1,NULLIF(braddress2,''))
         , braddress2 = braddress3 
         , braddress3 = braddress4
         , braddress4 = braddress5
         , braddress5 = NULL
    rudy.ca | @rudydotca
    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
  •