-- Changes to hs_hr_emp_directory_email table

ALTER TABLE `hs_hr_emp_directory_email` DROP FOREIGN KEY `fk_hs_hr_emp_directory_email_hs_hr_employee` ;

ALTER TABLE hs_hr_emp_directory_email DROP PRIMARY KEY;

ALTER TABLE hs_hr_emp_directory_email ADD COLUMN `process_date` DATE DEFAULT NULL AFTER `status`;

ALTER TABLE hs_hr_emp_directory_email ADD PRIMARY KEY (emp_number, process_date);

Update hs_hr_emp_directory_email e set e.process_date = (select jv.joined_date from
    (select emp.emp_number, emp.joined_date from hs_hr_employee emp left join hs_hr_emp_directory_email edm ON (edm.emp_number = emp.emp_number) where edm.emp_number = emp.emp_number) jv where jv.emp_number = e.emp_number);


