address_data.sql

-- wrap in transaction
BEGIN;

-- copy only customer addresses to new customers.address table
INSERT INTO customers.address
SELECT *
FROM common.address
WHERE common.address.address_id IN (
        SELECT DISTINCT address_id
        FROM customers.customer
    );

-- copy only staff addresses to new staff.address table
INSERT INTO staff.address
SELECT COUNT(*)
FROM common.address
WHERE common.address.address_id IN (
        SELECT DISTINCT address_id
        FROM staff.staff
    );

-- copy only store addresses to new stores.address table
INSERT INTO stores.address
SELECT *
FROM common.address
WHERE common.address.address_id IN (
        SELECT DISTINCT address_id
        FROM stores.store
    );

-- check for extraneous data in common.address before deleting
SELECT *
FROM common.address
WHERE common.address.address_id NOT IN
		(SELECT DISTINCT address_id FROM customers.customer)
	AND common.address.address_id NOT IN
		(SELECT DISTINCT address_id FROM staff.staff)
	AND common.address.address_id NOT IN
		(SELECT DISTINCT address_id FROM stores.store);
COMMIT;