|
|
|
Forum Newbie
Group: Forum Members
Last Login: 3/14/2008 8:15:26 AM
Posts: 7,
Visits: 30
|
|
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
THANKS IN ADVANCE
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 8/26/2008 4:53:36 PM
Posts: 50,
Visits: 220
|
|
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)
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 8:21:39 PM
Posts: 214,
Visits: 387
|
|
|
The MERGE INTO syntax is supported in Teradata release 12.0.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 3/14/2008 8:15:26 AM
Posts: 7,
Visits: 30
|
|
Hi,
THANKS to your valuable reply for the merge statement...
THANKS IN ADVANCE
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 8/26/2008 8:44:16 AM
Posts: 35,
Visits: 232
|
|
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.
Regards:
Monika
|
|
|
|