Special thanks to Linda Ilovic of Hofstra University for providing this script.
In the General 7.0 upgrade, a primary key constraint will be added to the email table GOREMAL.
However, if there a duplicate records already exist, the upgrade will fail.
To clean up this table, run the script below.
This script will delete mail records using the following algorithm:
1. If there are 1 active and many inactive records, it will delete all inactives.
2.If more than 1 are active, it will delete all rows that ARE NOT preferred.
3. If more than 1 are active, and preferred indicator is NOT set, then it will delete
all records EXCEPT the most current.
4. All records that are not preferred with same activity date will be deleted arbitrarity based on ROWID
REM
REM---------------BEGIN SCRIPT--------------------
REM
REM delete where 1 record is inactive and other active
REM
delete from goremal a
where GOREMAL_STATUS_IND = 'I'
and not exists ( select count (*)
from goremal c
where c.goremal_pidm = a.goremal_pidm
and c.goremal_emal_code = a.goremal_emal_code
and GOREMAL_STATUS_IND = 'I'
and c.GOREMAL_EMAIL_ADDRESS = a.GOREMAL_EMAIL_ADDRESS
group by goremal_emal_code,goremal_Pidm,GOREMAL_EMAIL_ADDRESS
having count(*) > 1)
and exists ( select count (*)
from goremal b
where b.goremal_pidm = a.goremal_pidm
and b.goremal_emal_code = a.goremal_emal_code
and b.GOREMAL_EMAIL_ADDRESS = a.GOREMAL_EMAIL_ADDRESS
group by goremal_emal_code,goremal_Pidm,GOREMAL_EMAIL_ADDRESS
having count(*) > 1);
REM
REM where two records are inactive or two are active,
REM if one is preferred, delete the other
REM unless it is flagged as preferred
REM
delete from goremal a
where exists (select 'X'
from goremal b
where b.goremal_pidm = a.goremal_pidm
and b.goremal_emal_code = a.goremal_emal_code
and b.GOREMAL_EMAIL_ADDRESS = a.GOREMAL_EMAIL_ADDRESS
and b.GOREMAL_PREFERRED_IND = 'Y')
and a.GOREMAL_PREFERRED_IND = 'N'
and exists ( select count (*)
from goremal c
where c.goremal_pidm = a.goremal_pidm
and c.goremal_emal_code = a.goremal_emal_code
and c.GOREMAL_EMAIL_ADDRESS = a.GOREMAL_EMAIL_ADDRESS
group by goremal_emal_code,goremal_Pidm,GOREMAL_EMAIL_ADDRESS
having count(*) > 1);
REM
REM remaining records are both active or both inactive,
REM and both preferred or neither preferred. Delete oldest
REM
delete from goremal a
where GOREMAL_ACTIVITY_DATE <> (select max(GOREMAL_ACTIVITY_DATE)
from goremal b
where b.goremal_pidm = a.goremal_pidm
and b.goremal_emal_code = a.goremal_emal_code
and b.GOREMAL_EMAIL_ADDRESS = a.GOREMAL_EMAIL_ADDRESS)
and exists ( select count (*)
from goremal c
where c.goremal_pidm = a.goremal_pidm
and c.goremal_emal_code = a.goremal_emal_code
and c.GOREMAL_EMAIL_ADDRESS = a.GOREMAL_EMAIL_ADDRESS
group by goremal_emal_code,goremal_Pidm,GOREMAL_EMAIL_ADDRESS
having count(*) > 1);
REM
REM any records left will have the same activity date. Choose one arbitrarily.
REM
delete from goremal a
where ROWID <> (select max(ROWID)
from goremal b
where b.goremal_pidm = a.goremal_pidm
and b.goremal_emal_code = a.goremal_emal_code
and b.GOREMAL_EMAIL_ADDRESS = a.GOREMAL_EMAIL_ADDRESS)
and exists ( select count (*)
from goremal c
where c.goremal_pidm = a.goremal_pidm
and c.goremal_emal_code = a.goremal_emal_code
and c.GOREMAL_EMAIL_ADDRESS = a.GOREMAL_EMAIL_ADDRESS
group by goremal_emal_code,goremal_Pidm,GOREMAL_EMAIL_ADDRESS
having count(*) > 1);
REM
REM ------------------END SCRIPT------------------------
REM
Once the script runs successfully, you can verify that the table is cleaned
up by running the following query:
REM
REM Check to make sure they're all gone.
REM
select count(*),goremal_emal_code, goremal_pidm,substr(GOREMAL_EMAIL_ADDRESS,1,30)
from goremal
group by goremal_emal_code,goremal_Pidm,GOREMAL_EMAIL_ADDRESS
having count(*) > 1
order by goremal_Pidm;
