Regarding Teradata MERGE statement

Database
Enthusiast

Regarding Teradata MERGE statement

Hi,

Can you please let me know that can I use merge statement in teradata version 06.02.0250?

As I am not sure that merge statement is supported or not in that version of teradata, so I have used a statement of the following :

update fact_upsd_new
from (select dt_key
,cust_name
,itm_cd_key
,itm_cd
,opr_cd
,opr_dsc
,chpst_type
,prod_fmly
,qtr_key
,fscl_yr_qtr_int_nbr
,mo_key
,fscl_yr_mo_nbr
,frst_day_fscl_mo_dt
,sls_org_grp_cd
,sls_org_grp_dsc
,pasd_cgid
,pasd_cme
,pasd_uncnstr
,geo_frct_qty
,pasd_grs_bllg
,pasd_rgid_blg
,pasd_uns_blg
,pasd_zups
from fact_upsd_final_tmp_new) fuft
set pasd_cgid = fuft.pasd_cgid,
pasd_cme = fuft.pasd_cme,
pasd_uncnstr = fuft.pasd_uncnstr,
geo_frct_qty = fuft.geo_frct_qty,
pasd_grs_bllg = fuft.pasd_grs_bllg,
pasd_rgid_blg = fuft.pasd_rgid_blg,
pasd_uns_blg = fuft.pasd_uns_blg,
pasd_zups = fuft.pasd_zups
where dt_key = fuft.dt_key and
cust_name = fuft.cust_name and
itm_cd_key = fuft.itm_cd_key and
itm_cd = fuft.itm_cd and
opr_cd = fuft.opr_cd and
opr_dsc = fuft.opr_dsc and
chpst_type = fuft.chpst_type and
prod_fmly = fuft.prod_fmly and
qtr_key = fuft.qtr_key and
fscl_yr_qtr_int_nbr = fuft.fscl_yr_qtr_int_nbr and
mo_key = fuft.mo_key and
fscl_yr_mo_nbr = fuft.fscl_yr_mo_nbr and
frst_day_fscl_mo_dt = fuft.frst_day_fscl_mo_dt and
sls_org_grp_cd = fuft.sls_org_grp_cd and
sls_org_grp_dsc = fuft.sls_org_grp_dsc

but I am getting an error that column dt_key is ambiguous.

The table structures are specified below :

CREATE MULTISET TABLE KCHANDR.fact_upsd_new ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
dt_key INTEGER NOT NULL,
cust_name VARCHAR(14) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
itm_cd_key INTEGER NOT NULL,
itm_cd VARCHAR(21) CHARACTER SET LATIN NOT CASESPECIFIC,
opr_cd VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
opr_dsc VARCHAR(45) CHARACTER SET LATIN NOT CASESPECIFIC,
chpst_type VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
prod_fmly VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
qtr_key INTEGER,
fscl_yr_qtr_int_nbr INTEGER,
mo_key INTEGER NOT NULL,
fscl_yr_mo_nbr INTEGER,
frst_day_fscl_mo_dt TIMESTAMP(0),
sls_org_grp_cd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
sls_org_grp_dsc CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
pasd_cgid DECIMAL(15,3),
pasd_cme DECIMAL(15,3),
pasd_uncnstr DECIMAL(15,3),
geo_frct_qty DECIMAL(15,3),
pasd_grs_bllg DECIMAL(15,3),
pasd_rgid_blg DECIMAL(15,3),
pasd_uns_blg DECIMAL(15,3),
pasd_zups DECIMAL(15,3))
UNIQUE PRIMARY INDEX xfact_upsd01 ( dt_key ,cust_name ,itm_cd_key ,itm_cd, opr_cd, opr_dsc, chpst_type, prod_fmly, qtr_key, fscl_yr_qtr_int_nbr, mo_key ,fscl_yr_mo_nbr, frst_day_fscl_mo_dt, sls_org_grp_cd, sls_org_grp_dsc );

CREATE MULTISET TABLE KCHANDR.fact_upsd_final_tmp_new ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
cust_name VARCHAR(14) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
itm_cd_key INTEGER NOT NULL,
itm_cd VARCHAR(21) CHARACTER SET LATIN NOT CASESPECIFIC,
opr_cd VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
opr_dsc VARCHAR(45) CHARACTER SET LATIN NOT CASESPECIFIC,
chpst_type VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
prod_fmly VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
qtr_key INTEGER,
fscl_yr_qtr_int_nbr INTEGER,
mo_key INTEGER NOT NULL,
fscl_yr_mo_nbr INTEGER,
frst_day_fscl_mo_dt TIMESTAMP(0),
sls_org_grp_cd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
sls_org_grp_dsc CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
pasd_cgid DECIMAL(15,3),
pasd_cme DECIMAL(15,3),
pasd_uncnstr DECIMAL(15,3),
geo_frct_qty DECIMAL(15,3),
pasd_grs_bllg DECIMAL(15,3),
pasd_rgid_blg DECIMAL(15,3),
pasd_uns_blg DECIMAL(15,3),
pasd_zups DECIMAL(15,3),
dt_key INTEGER NOT NULL
)
Unique PRIMARY Index xfact_upsd_tmp01 ( dt_key ,cust_name ,itm_cd_key ,itm_cd, opr_cd, opr_dsc, chpst_type, prod_fmly, qtr_key, fscl_yr_qtr_int_nbr, mo_key ,fscl_yr_mo_nbr, frst_day_fscl_mo_dt, sls_org_grp_cd, sls_org_grp_dsc );

Regards,
Koushik
2 REPLIES
Enthusiast

Re: Regarding Teradata MERGE statement

Hi,

I am able to resolve the update part. The query should be like below :

update fact_upsd_new
from (select dt_key
,cust_name
,itm_cd_key
,itm_cd
,opr_cd
,opr_dsc
,chpst_type
,prod_fmly
,qtr_key
,fscl_yr_qtr_int_nbr
,mo_key
,fscl_yr_mo_nbr
,frst_day_fscl_mo_dt
,sls_org_grp_cd
,sls_org_grp_dsc
,pasd_cgid
,pasd_cme
,pasd_uncnstr
,geo_frct_qty
,pasd_grs_bllg
,pasd_rgid_blg
,pasd_uns_blg
,pasd_zups
from fact_upsd_final_tmp_new) as fuft
set pasd_cgid = fuft.pasd_cgid,
pasd_cme = fuft.pasd_cme,
pasd_uncnstr = fuft.pasd_uncnstr,
geo_frct_qty = fuft.geo_frct_qty,
pasd_grs_bllg = fuft.pasd_grs_bllg,
pasd_rgid_blg = fuft.pasd_rgid_blg,
pasd_uns_blg = fuft.pasd_uns_blg,
pasd_zups = fuft.pasd_zups
where fact_upsd_new.dt_key = fuft.dt_key and
fact_upsd_new.cust_name = fuft.cust_name and
fact_upsd_new.itm_cd_key = fuft.itm_cd_key and
fact_upsd_new.itm_cd = fuft.itm_cd and
fact_upsd_new.opr_cd = fuft.opr_cd and
fact_upsd_new.opr_dsc = fuft.opr_dsc and
fact_upsd_new.chpst_type = fuft.chpst_type and
fact_upsd_new.prod_fmly = fuft.prod_fmly and
fact_upsd_new.qtr_key = fuft.qtr_key and
fact_upsd_new.fscl_yr_qtr_int_nbr = fuft.fscl_yr_qtr_int_nbr and
fact_upsd_new.mo_key = fuft.mo_key and
fact_upsd_new.fscl_yr_mo_nbr = fuft.fscl_yr_mo_nbr and
fact_upsd_new.frst_day_fscl_mo_dt = fuft.frst_day_fscl_mo_dt and
fact_upsd_new.sls_org_grp_cd = fuft.sls_org_grp_cd and
fact_upsd_new.sls_org_grp_dsc = fuft.sls_org_grp_dsc

But still I am not sure whether can I use merge statement in that version of teradata or not. Can you please let me know that. If I can use then what will be the exact syntax of it.

Regards,
Koushik
Junior Contributor

Re: Regarding Teradata MERGE statement

Hi Koushik,
pre-TD12 only supports merge for single rows, so you can't use it.
TD12 support (almost) full ANSI-MERGE

Dieter