How Do You Merge MySQL Columns?

[virginiamonkeys]

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.

[r937]

caution: back up your data before testing this

UPDATE custbranch
   SET braddress1 = CONCAT_WS(',',braddress1,braddress2)
     , braddress2 = braddress3 
     , braddress3 = braddress4
     , braddress4 = braddress5
     , braddress5 = NULL

[virginiamonkeys]

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.

[r937]

i guess you haven't played around with CONCAT_WS very much -- it works differently than CONCAT

;

[virginiamonkeys]

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?

[virginiamonkeys]

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' )

[virginiamonkeys]

This works at trimming that trailing ", ".

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

[r937]

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 --

UPDATE custbranch
   SET braddress1 = CONCAT_WS(',',braddress1,NULLIF(braddress2,''))
     , braddress2 = braddress3 
     , braddress3 = braddress4
     , braddress4 = braddress5
     , braddress5 = NULL