Teradata Update Table

Database

Teradata Update Table

UPDATE ETL_TEMP_REL_ENT.WORK_ETL_GAP_ADW_MBR_PCP --WPCP

FROM

(

SELECT NT13.BU_SUBR_NO

                   ,NT13.BU_DEP_NO

                   ,NT13.BL_GRP_NO_ID

                   ,NT13.BL_SUBGR_NO

                   ,NT13.BL_HCO_NO

                   ,NT13.BL_HMO_NO

                   ,NT13.BL_PCP_LOC_CD

                   ,NT13.BL_HMO_PROCESDT

                   ,NT13.LOAD_LOG_KEY

FROM ETL_temp_rel_ENT.LZ_CS90_VSBPNTE_NT13 NT13, CSA_LOAD_LOG CLL

WHERE   CLL.SUBJ_AREA_NM='MBR' AND

                      CLL.SOR_CD='809' AND

                      CLL.WORK_FLOW_NM= 'MBR_CS90_LZ_LOAD' AND 

                      CLL.PBLSH_IND='N' AND

                      CLL.LOAD_END_DTM='8888-12-31 12:00:00.000000'

) NT13

SET  WPCP.BU_SUBR_NBR  = NT13.BU_SUBR_NO

    ,WPCP.BU_DEP_NBR            = NT13.BU_DEP_NO

    ,ETL_TEMP_REL_ENT.WORK_ETL_GAP_ADW_MBR_PCP.BL_GRP_NO_ID      = NT13.BL_GRP_NO_ID

    ,ETL_TEMP_REL_ENT.WORK_ETL_GAP_ADW_MBR_PCP.BL_SUBGR_NBR       = NT13.BL_SUBGR_NO

    ,ETL_TEMP_REL_ENT.WORK_ETL_GAP_ADW_MBR_PCP.BL_HCO_NBR             = NT13.BL_HCO_NO

    ,ETL_TEMP_REL_ENT.WORK_ETL_GAP_ADW_MBR_PCP.BL_HMO_NBR            = NT13.BL_HMO_NO

    ,ETL_TEMP_REL_ENT.WORK_ETL_GAP_ADW_MBR_PCP.BL_PCP_LOC_CD   = NT13.BL_PCP_LOC_CD

    ,ETL_TEMP_REL_ENT.WORK_ETL_GAP_ADW_MBR_PCP.PCP_PRCS_DT       = NT13.BL_HMO_PROCESDT

    ,ETL_TEMP_REL_ENT.WORK_ETL_GAP_ADW_MBR_PCP.DT_PRCS_NBR          = NT13.BL_HMO_PROCESDT

    ,ETL_TEMP_REL_ENT.WORK_ETL_GAP_ADW_MBR_PCP.UPDTD_LOAD_LOG_KEY = NT13.LOAD_LOG_KEY

    ,ETL_TEMP_REL_ENT.WORK_ETL_GAP_ADW_MBR_PCP.TRNSCTN_CD='U'

WHERE

     TRIM(ETL_TEMP_REL_ENT.WORK_ETL_GAP_ADW_MBR_PCP.BU_SUBR_NO)=TRIM(NT13.BU_SUBR_NO) AND

     TRIM(ETL_TEMP_REL_ENT.WORK_ETL_GAP_ADW_MBR_PCP.BU_DEP_NO)=TRIM(NT13.BU_DEP_NO) AND

     TRIM(ETL_TEMP_REL_ENT.WORK_ETL_GAP_ADW_MBR_PCP.BL_GRP_NO_ID)=TRIM(NT13.BL_GRP_NO_ID) AND

     TRIM(ETL_TEMP_REL_ENT.WORK_ETL_GAP_ADW_MBR_PCP.BL_SUBGR_NO)=TRIM(NT13.BL_SUBGR_NO)

Updating table rows by comparing the load log keys of NT13 tabel and CSA_LOAD_LOG table LLK in select. Select is executing  but stuck with the SET and Where part. I need to write the join condition based on the columns mentioned in where. I am new to Teradata. Pls help.

Regards,

Karthik.

1 REPLY
Enthusiast

Re: Teradata Update Table

You should give

1) ALIAS NAME AFTER UPDATE,

2) AFTER FROM MENTION THE TABLENAMES

3)TABLENAME.COL IN SET IS NOT REQUIRED

4)TRY TO USE ALIASES EVERYWHERE IF U HAVE CREATED ONE, SOMETIMES IT MIGHT CAUSE PRODUCT JOINS IF U USE ALIAS AND TABLENAME ALTERNATIVELY

UPDATE 
WPCP
FROM
ETL_TEMP_REL_ENT.WORK_ETL_GAP_ADW_MBR_PCP WPCP,
(

SELECT NT13.BU_SUBR_NO

,NT13.BU_DEP_NO

,NT13.BL_GRP_NO_ID

,NT13.BL_SUBGR_NO

,NT13.BL_HCO_NO

,NT13.BL_HMO_NO

,NT13.BL_PCP_LOC_CD

,NT13.BL_HMO_PROCESDT

,NT13.LOAD_LOG_KEY

FROM ETL_temp_rel_ENT.LZ_CS90_VSBPNTE_NT13 NT13, CSA_LOAD_LOG CLL

WHERE CLL.SUBJ_AREA_NM='MBR' AND

CLL.SOR_CD='809' AND

CLL.WORK_FLOW_NM= 'MBR_CS90_LZ_LOAD' AND

CLL.PBLSH_IND='N' AND

CLL.LOAD_END_DTM='8888-12-31 12:00:00.000000'

) NT13

SET BU_SUBR_NBR = NT13.BU_SUBR_NO

,BU_DEP_NBR = NT13.BU_DEP_NO

,BL_GRP_NO_I D = NT13.BL_GRP_NO_ID

,BL_SUBGR_NB R = NT13.BL_SUBGR_NO

,BL_HCO_NBR = NT13.BL_HCO_NO

,BL_HMO_NBR = NT13.BL_HMO_NO

,BL_PCP_LOC_ CD = NT13.BL_PCP_LOC_CD

,PCP_PRCS_DT = NT13.BL_HMO_PROCESDT

,DT_PRCS_NBR = NT13.BL_HMO_PROCESDT

,UPDTD_LOAD_ LOG_KEY = NT13.LOAD_LOG_KEY

,TRNSCTN_CD= 'U'

WHERE

TRIM(WPCP.BU_SUB R_NO)=TRIM(NT13.BU_SUBR_NO) AND

TRIM(WPCP.BU_DEP _NO)=TRIM(NT13.BU_DEP_NO) AND

TRIM(WPCP.BL_GRP _NO_ID)=TRIM(NT13.BL_GRP_NO_ID) AND

TRIM(WPCP.BL_SUB GR_NO)=TRIM(NT13.BL_SUBGR_NO)

Try this.... The tablenames whatever you have used seems familiar to me.. I think i have been working on them too :)

Cheers,

Mani