CONVERT MERGE STATMENT INTO UPSERT STATMENT

Connectivity
Enthusiast

CONVERT MERGE STATMENT INTO UPSERT STATMENT

Hi,

CAn any body help me out to convert the below MERGE statement into UPSERT Statment(in teradata)...

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 );

THANKS IN ADVANCE
4 REPLIES
Enthusiast

Re: CONVERT MERGE STATMENT INTO UPSERT STATMENT

You can use Update & Insert strategy in place of MERGE INTO. However, merge into is also valid syntax in Teradata. I am not sure why you need change the Merge into this but you may use something like below.

Update portion:

Update cop_best_email
From ( 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 --- (You hae to change the oracle syntax of LOJ)
Left Outer Join acd_person a
ON b.prs_cd_id = a.prs_cd_id
AND b.upd_ts >= ld_last_insert
AND b.upd_ts <= ld_max_dt) sl
SET
prs_id = sl.prs_id, ----- ( you cannot use Pl.prs_id for the target table)
apple_id = sl.apple_id,
prs_eff_upd_ts = sl.effective_update_date,
prs_email_addr = sl.email_address,
acd_cre_ts = sl.date_create,
acd_upd_ts = sl.date_update,
upd_ts = ld_max_dt
WHERE prs_cd_id = sl.prs_cd_id ;

Insert portion:

INSERT INTO cop_best_email ( give the column list)
SELECT 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
From ( Select
b.prs_cd_id AS prs_cd_id
, b.apple_id AS apple_id
, b.effective_update_date AS effective_update_date
, b.date_create AS date_create
, b.date_update AS date_update
, b.email_address AS email_address
, a.prs_id As prs_id
FROM sl_acd_best_email b --- (You hae to change the oracle syntax of LOJ)
Left Outer Join acd_person a
ON b.prs_cd_id = a.prs_cd_id
AND b.upd_ts >= ld_last_insert
AND b.upd_ts <= ld_max_dt) sl
WHERE NOT EXISTS
( Select 1
From cop_best_email pl
WHERE pl.prs_cd_id = sl.prs_cd_id)

Enthusiast

Re: CONVERT MERGE STATMENT INTO UPSERT STATMENT

The MERGE INTO syntax is supported in Teradata release 12.0.
Enthusiast

Re: CONVERT MERGE STATMENT INTO UPSERT STATMENT

Hi,
THANKS to your valuable reply for the merge statement...
Enthusiast

Re: CONVERT MERGE STATMENT INTO UPSERT STATMENT

I have read some where that upsert will work same as merge.
UPDATE
SET
WHERE xyz.col =
AND
ELSE INSERT INTO );
I think this will solve ur issue.