copy_columns_to_new_table.sql

-- MIGRATION1: (copy data)

-- create new table
CREATE TABLE cdm.adresses (
  id BIGINT(20) AUTO_INCREMENT PRIMARY KEY,
  enrichment_erp_id BIGINT(20) UNIQUE NOT NULL, 
  latitude DECIMAL(20,6) NOT NULL,
  longitude DECIMAL(20,6) NOT NULL,
  zip_code VARCHAR(20) NOT NULL,
  street VARCHAR(255),
  street2 VARCHAR(255),
  city VARCHAR(128),
  country_code VARCHAR(2)
);


-- create foreign key
ALTER TABLE cdm.enrichment_erp ADD COLUMN address_id BIGINT(20), 
ADD FOREIGN KEY (address_id) REFERENCES cdm.adresses(id);


-- move data
INSERT INTO cdm.adresses (enrichment_erp_id, latitude, longitude, zip_code) 
SELECT DISTINCT id, latitude, longitude, zip_code FROM cdm.enrichment_erp;


-- update foreign key to first table
UPDATE cdm.enrichment_erp
SET address_id = (
  SELECT id
  FROM cdm.adresses
  WHERE enrichment_erp_id = cdm.enrichment_erp.id
);



-- MIGRATION2: (remove old columns)

-- remove source key form target table
ALTER TABLE cdm.adresses
DROP COLUMN enrichment_erp_id;

-- remove source copied columns
ALTER TABLE cdm.enrichment_erp
DROP latitude, 
DROP longitude,
DROP zip_code;