beterthanlife beterthanlife - 1 year ago 60
SQL Question

insert from another table and then link tables by id

I need to refactor a Company table so that the address information is moved into a dedicated Address table.

So I figure I need to do something like this to create the new address records.

INSERT INTO address (city, country, ...)
SELECT city,country, ... FROM company

But how do I update the company table with the id of the newly created address records in order to maintain referential integrity?

There are several duplicate addresses so I cant just join on the city, country fields, etc.


As alluded to by juergen d, the way I got round this is to create a temporary company_id column in the address table:

ALTER TABLE address ADD company_id INTEGER

INSERT INTO address (city,country,company_id)
SELECT city,country,id from company

UPDATE company c set c.address_id = (select from address a where a.company_id =

ALTER TABLE address DROP COLUMN company_id;

Answer Source

You could use a company_id in your address table.

INSERT INTO address (company_id, city, country, ...)
SELECT id, city, country, ... 
FROM company

Then there is no need to add any address_id to the company table.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download