Migrating data from an old to a new schema

Question

I have two tables from two different schemas. I want to move data from a table in the old schema to the table in the new schema.

SET foreign_key_checks = 0;

INSERT into newdb.Addresses (addressID, contactID, primaryFlag, type, address1, address2, poBox, city, state, zip, country, email)
SELECT address_id, contact_id, is_primary_address, 

    (SELECT address_type
    FROM test.address_types, test.contact_addresses
    WHERE test.contact_addresses.address_type_id = test.address_types.address_type_id),

    address_line_1, address_line_2, address_line_2, address_city, address_state, address_zip,

    (SELECT country
    FROM test.countries, test.contact_addresses
    WHERE test.contact_addresses.address_country_id = test.countries.country_id),

    address_email

FROM test.contact_addresses;

Sorry if it's sloppily typed. Basically, when I run the query, it says that the subquery returns more than one row. How do I restructure the query so that what is returned in the subqueries is the row that the engine is currently iterating through (therefore, only one row is returned)?

Answer 1

You dont need the test.contact_addresses in the subqueries:

SELECT address_id, contact_id, is_primary_address, 

     (SELECT address_type
      FROM test.address_types AS at
      WHERE at.address_type_id = ca.address_type_id),

address_line_1, ....

FROM test.contact_addresses AS ca;

This way, SQL selects the one that equals the adress_type_id in the outer query.