Query problem - insert from multiple colums to single column

[LastWords]

Hi

I have two tables, addresses_old and addresses_new - I want to transfer the data from addresses_old into addresses_new - the schema for each table is different, see below:

CREATE TABLE `addresses_old` (
  `key` int(11) NOT NULL,
  `address1` varchar(32) default NULL,
  `address2` varchar(32) default NULL,
  `address3` varchar(32) default NULL,
  PRIMARY KEY  (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `addresses_new` (
  `address_id` int(11) NOT NULL auto_increment,
  `address` varchar(32) default NULL
  PRIMARY KEY  (`address_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I want to copy all columns address1, address2, address3 from addresses_old into address in addresses_new table. How do I do this?

I have tried a simple insert, like:

INSERT INTO addresses_new (address)
SELECT addresses_old.address1

But this only inserts the address1 column, how do I insert all of them at once?

Any help much appreciated. Thanks.


by Lastwords,

Maentwrog (n.Welsh): Celtic word for a computer spelling mistake.

 

[feherke]

Hi

As far as I know, not possible with a single [tt]select[/tt]. So the closest to "at once" would be a [tt]union[/tt] :

[b]insert[/b] [b]into[/b] addresses_new [teal]([/teal]address[teal])[/teal]
[b]select[/b] address1 [b]from[/b] addresses_old
union
[b]select[/b] address2 [b]from[/b] addresses_old
union
[b]select[/b] address3 [b]from[/b] addresses_old

Feherke.

http://free.rootshell.be/~feherke/

 

[r937]

i think the "all at once" means concatenation...

INSERT 
  INTO addresses_new 
     ( address )
SELECT CONCAT_WS(' ',address1,address2,address3)
  FROM addresses_old

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon