whenever oserror exit failure whenever sqlerror exit failure -- Delete Duplicates delete from set b where rowid != ( select min(rowid) from set a where a.PLATFORM = b.PLATFORM and a.CUST_ID = b.CUST_ID and a.DIV_ID = b.DIV_ID and a.BEN_ID = b.BEN_ID and a.EFF_DATE = b.EFF_DATE); commit; merge into tar using ( select distinct set1.EXTRACT, set1.ACTION, set1.PLATFORM, set1.APPL, set1.BEN_ID, set1.EFF_DATE, set1.END_DATE, set1.MAJOR, set1.ASO, set1.FSA lkp1.SOLD, set1.CUST_ID, set1.DIV_ID, set1.CUST_KEY, set1.FT, set1.VOID, set1.MINOR from (select distinct set.EXTRACT, set.ACTION, set.PLATFORM, set.APPL, set.BEN_ID, set.EFF_DATE, set.END_DATE, lkp.MAJOR, set.ASO, set.FSA, lkp.SOLD, set.CUST_ID, set.DIV_ID, set.CUST_KEY, set.FT, set.VOID, set.MINOR, set.product, set.MAJORCD, set.market, set.type_cd from set, (select distinct PLATFORM, MINOR, typ, MAJOR, SOLD,PRODUCT from tab1) lkp where set.PLATFORM = lkp.PLATFORM(+) and set.type_cd = lkp.typ(+) and set.product = lkp.product(+) and set.MAJOR = lkp.MINOR(+)) set1, (select distinct PLATFORM, MINOR, typ, aso, market, SOLD,PRODUCT from tab1) lkp1 where set1.PLATFORM = lkp1.PLATFORM(+) and set1.type_cd = lkp1.typ(+) and set1.aso = lkp1.aso(+) and set1.market= lkp1.market(+) and set1.product = lkp1.product(+) and set1.MAJOR = lkp1.MINOR(+) ) src on ( nvl(tar.PLATFORM, 'AA') = nvl(src.PLATFORM, 'AA') and nvl(tar.CUST_ID, 'AA') = nvl(src.CUST_ID, 'AA') and nvl(tar.DIV_ID, 'AA') = nvl(src.DIV_ID, 'AA') and nvl(tar.BEN_ID, 'AA') = nvl(src.BEN_ID, 'AA') ) when not matched then insert ( EXTRACT, ACTION, PLATFORM, APPL, BEN_ID, EFF_DATE, END_DATE, MAJOR, ASO, FSA, SELL_ID, SERID, BASE_ID, CUST_ID, DIV_ID, CUST_KEY, FT, VOID, MINOR, TIMESTAMP ) values ( src.EXTRACT, src.ACTION, src.PLATFORM, src.APPL, src.BEN_ID, src.EFF_DATE, src.END_DATE, src.MAJOR, src.ASO, src.FSA, src.SOLD, src.SOLD, src.SOLD, src.CUST_ID, src.DIV_ID, src.CUST_KEY, src.FT, src.VOID, src.MINOR, sysdate ) when matched then update set tar.EXTRACT = src.EXTRACT, tar.ACTION = src.ACTION, tar.EFF_DATE = src.EFF_DATE, tar.END_DATE = src.END_DATE, tar.MAJOR = src.MAJOR, tar.ASO = src.ASO, tar.FSA = src.FSA, tar.SELL_id = src.SOLD, tar.SER_id = src.SOLD, tar.BASE = src.SOLD, tar.CUST_KEY = src.CUST_KEY, tar.FT = src.FT, tar.VOID = src.VOID, tar.MINOR = src.MINOR, tar.TIMESTAMP = sysdate; commit;