CONVERT THE MERGE STATEMENT INTO UPSERT STATEMENT

Connectivity
Enthusiast

CONVERT THE MERGE STATEMENT INTO UPSERT STATEMENT

MERGE INTO cop_best_email pl
USING
(SELECT
b.prs_cd_id, b.apple_id, b.effective_update_date,
b.date_create, b.date_update, b.email_address,
a.prs_id
FROM sl_acd_best_email b , acd_person a
WHERE b.prs_cd_id = a.prs_cd_id(+)
AND b.upd_ts >= ld_last_insert
AND b.upd_ts <= ld_max_dt) sl
ON (pl.prs_cd_id = sl.prs_cd_id)
WHEN MATCHED THEN
UPDATE SET
pl.prs_id = sl.prs_id,
pl.apple_id = sl.apple_id,
pl.prs_eff_upd_ts = sl.effective_update_date,
pl.prs_email_addr = sl.email_address,
pl.acd_cre_ts = sl.date_create,
pl.acd_upd_ts = sl.date_update,
pl.upd_ts = ld_max_dt
WHEN NOT MATCHED THEN
INSERT ( pl.prs_cd_id, pl.prs_id, pl.apple_id,
pl.acd_cre_ts, pl.acd_upd_ts, pl.prs_eff_upd_ts,
pl.prs_email_addr, pl.cre_ts, pl.upd_ts )
VALUES ( sl.prs_cd_id, sl.prs_id, sl.apple_id,
sl.date_create, sl.date_update, sl.effective_update_date,
sl.email_address, ld_max_dt, ld_max_dt );