Insert/Update in a single statement

Database
Enthusiast

Insert/Update in a single statement

Can i do these two statement in a single query

INSERT INTO ECR_RTL_ECAR_DEV.CTL_OPEN_DT

SEL PROC_YYMM_DT,CCCP_KEY, CAST(CAST(CAST(ACC_OPEN_DT AS DATE FORMAT 'YYYYMM') AS CHAR(6)) AS INTEGER) as ACC_OPEN_YYMM_DT

FROM ECR_RTL_ECAR_DEV.CORE_LOANS

WHERE CCCP_KEY NOT IN

(SEL CCCP_KEY FROM ECR_RTL_ECAR_DEV.CTL_OPEN_DT)

AND PROC_YYMM_DT IN

(SEL CURR_PER FROM ECR_RTL_ECAR_DEV.CTL_CURR_PERIOD

WHERE RUN_FLAG = 'Y')

AND ACC_OPEN_DT IS NOT NULL

UPDATE B FROM ECR_RTL_ECAR_DEV.CTL_OPEN_DT B,

(SELECT PROC_YYMM_DT, ACC_OPEN_DT,CCCP_KEY

FROM ECR_RTL_ECAR_DEV.CORE_LOANS

WHERE PROC_YYMM_DT IN(SEL CURR_PER FROM ECR_RTL_ECAR_DEV.CTL_CURR_PERIOD)) C

SET ACC_OPEN_YYMM_DT = C.ACC_OPEN_DT

WHERE B.PROC_YYMM_DT = C.PROC_YYMM_DT

AND B.CCCP_KEY = C.CCCP_KEY

AND ACC_OPEN_DT IS NOT NULL
3 REPLIES
Enthusiast

Re: Insert/Update in a single statement

Take a look at the MERGE INTO statement as enhanced for Teradata 12.0.
Enthusiast

Re: Insert/Update in a single statement

i cant find any proper material fro merge into .. can some1 enlighten me...?
Enthusiast

Re: Insert/Update in a single statement

You will find complete documentation for MERGE INTO in the 12.0 version of "Teradata SQL Reference: Data Manipulation Statements" (B035-1146-067A).