Total Pageviews

2014/02/17

Oracle MERGE statement

If you would like to select rows from one or more sources for update or insertion into a table, you can make good use of MERGE statement. 
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: