homepage Welcome to WebmasterWorld Guest from 23.21.9.44
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
How Do You Merge MySQL Columns?
merge mysql columns rows tables
sunshineteam




msg:4003260
 9:46 pm on Oct 7, 2009 (gmt 0)

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.

 

SanDiego Art




msg:4003321
 11:43 pm on Oct 7, 2009 (gmt 0)

I'm not sure how to do it purely in SQL, but if you write a script in PHP (or another language) it is pretty simple...

To combine line1 & line 2, you would simply concatenate these values together after the SELECT statement, then UPDATE the new line1 back to the db.

Something like (pseudo code):

WHILE counter < Results in db
{
line1 = braddress1 result from db
line2 = braddress2 result from db
line3 = "
line4 = "
line5 = "

newline1 = line1
if (line2 not equal to "")
newline1 = newline1 & ", " & line2

UPDATE db
-> braddress1=newline1
-> braddress2=line3
-> braddress2=line4
etc...
}

stajer




msg:4003363
 12:57 am on Oct 8, 2009 (gmt 0)

try experimenting with this:

update table
set braddress1 = braddress1 + ', ' + braddress2
where len(braddress2) > 0

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved