You can specify conditions to determine whether to update or insert into the target table.
Semantics
- INTO Clause
- your target table
- USING Clause
- your data source
- ON Clause
- set your conditions in on clause
- merge_update_clause
- specifies the new column values of the target table
- merge_insert_clause
- specifies values to insert into the column of the target table if the condition of the ON clause is false
Here is a practical example:
MERGE INTO FMS451FA T1
USING(
SELECT SUBSTR(ROC_YM,1,3) AS ROC_YR
,SUBSTR(ROC_YM,4,2) AS ROC_MM
,CUM_INCM_TAX --當月收入(2)-稅課收入
,CUM_NON_INCM_TAX --當月收入(2)-非稅課收入(含其他)
,CUM_INCM_LOAN --當月收入(2)-融資財源
,CUM_TTL_INCM --當月收入(2)-合計
,CUM_TTL_PAY_AMT --當月支出(3)
FROM FMS406VE
) T2
ON (T1.ROC_YM = T2.ROC_YR||T2.ROC_MM)
WHEN MATCHED THEN UPDATE SET T1.CUM_INCM_TAX = T2.CUM_INCM_TAX, --稅課收入
T1.CUM_NON_INCM_TAX = T2.CUM_NON_INCM_TAX,--非稅課收入(含其他)
T1.CUM_INCM_LOAN = T2.CUM_INCM_LOAN,--融資財源
T1.CUM_TTL_INCM = T2.CUM_TTL_INCM,--合計
T1.CUM_TTL_PAY_AMT = T2.CUM_TTL_PAY_AMT--當月支出(3)
WHEN NOT MATCHED THEN INSERT (T1.ROC_YM,
T1.CUM_INCM_TAX,
T1.CUM_NON_INCM_TAX,
T1.CUM_INCM_LOAN,
T1.CUM_TTL_INCM,
T1.CUM_TTL_PAY_AMT)
VALUES(T2.ROC_YR||T2.ROC_MM,
T2.CUM_INCM_TAX,
T2.CUM_NON_INCM_TAX,
T2.CUM_INCM_LOAN,
T2.CUM_TTL_INCM,
T2.CUM_TTL_PAY_AMT);
No comments:
Post a Comment