SQL Update

Database

SQL Update

Hi,

I am new to Teradata, and I need to do a SQL update. Based on the Syntax given in the Manual, I did write this SQL, but somehow this doesn't seem to work. Please let me know where I am going wrong?

Thanks in Advance.

UPDATE MSI_SUPPORT_TABLES.TBL_PERF_HIST_2_TEST
from ( select a12.FSC_WK_END_DT FSC_WK_END_DT,
a11.PRD_GRP_NBR PRD_GRP_NBR,
a12.CMA_LCT_CD CMA_LCT_CD,
sum(a11.TOT_SAL_AMT) SALES,
sum(a11.TOT_CST_AMT) COST
from ABC.PGP_SMY a11
join ABC.LCT_HST a12
on (a11.FSC_WK_END_DT = a12.FSC_WK_END_DT and
a11.LCT_NBR = a12.LCT_NBR)
group by a12.FSC_WK_END_DT,
a11.PRD_GRP_NBR,
a12.CMA_LCT_CD
) as A, MSI_SUPPORT_TABLES.TBL_PERF_HIST_2_TEST as b
set TBL_PERF_HIST_2_TEST.CMA_LCT_CD = a.cma_lct_cd, TBL_PERF_HIST_2_TEST.TOT_SAL_AMT = a.SALES, TBL_PERF_HIST_2_TEST.TOT_CST_AMT = A.COST
where b.fsc_wk_end_dt = a.fsc_wk_end_dt
and b.prd_grp_nbr = a.prd_grp_nbr

Regards
2 REPLIES
Enthusiast

Re: SQL Update

The following is the working version of the SQL:

UPDATE b
from ( select a12.FSC_WK_END_DT FSC_WK_END_DT,
a11.PRD_GRP_NBR PRD_GRP_NBR,
a12.CMA_LCT_CD CMA_LCT_CD,
sum(a11.TOT_SAL_AMT) SALES,
sum(a11.TOT_CST_AMT) COST
from ABC.PGP_SMY a11
join ABC.LCT_HST a12
on (a11.FSC_WK_END_DT = a12.FSC_WK_END_DT and
a11.LCT_NBR = a12.LCT_NBR)
group by a12.FSC_WK_END_DT,
a11.PRD_GRP_NBR,
a12.CMA_LCT_CD
) as A, MSI_SUPPORT_TABLES.TBL_PERF_HIST_2_TEST as b
set CMA_LCT_CD = a.cma_lct_cd,
TOT_SAL_AMT = a.SALES,
TOT_CST_AMT = A.COST
where b.fsc_wk_end_dt = a.fsc_wk_end_dt
and b.prd_grp_nbr = a.prd_grp_nbr

Re: SQL Update

Thank You.

Regards
Ravi