oracle merge into 的用法详解+实例

作用：merge into 解决用B表跟新A表数据，如果A表中没有，则把B表的数据插入A表；

语法：

MERGE INTO [your table-name] [rename your table here]

USING ( [write your query here] )[rename your query-sql and using just like a table]

ON ([conditional expression here] AND [...]...)

WHEN MATHED THEN [here you can execute some update sql or something else ]

WHEN NOT MATHED THEN [execute something else here ! ]

-------------------------------------实例-----------------------------------------------------------------

merge into tfa_alarm_act_nms a
using (select FP0,FP1,FP2,FP3,REDEFINE_SEVERITY
from tfa_alarm_status) b
on (a.fp0=b.fp0 and a.fp1=b.fp1 and a.fp2=b.fp2 and a.fp3=b.fp3)
when matched then update set a.redefine_severity=b.redefine_severity
when not matched then insert (a.fp0,a.fp1,a.fp2,a.fp3,a.org_severity,a.redefine_severity,a.event_time
,a.int_id)
values (b.fp0,b.fp1,b.fp2,b.fp3,b.REDEFINE_SEVERITY,b.redefine_severity,sysdate,7777778);

作用：利用表 tfa_alarm_status跟新表tfa_alarm_act_nms 的b.redefine_severity，条件是a.fp0=b.fp0 and a.fp1=b.fp1 and a.fp2=b.fp2 and a.fp3=b.fp3，如果tfa_alarm_act_nms表中没有该条件的数据就插入。

如果你的数据量很大，此sql效率非常高。

 

 

自己做过的Demo

merge into masadw.tb_dw_ac_not_app_spt_old a
    using (select t.user_id,t.acct_item_id,t.receivable_fee,t.acct_cycle_id
             from masadw.tb_ac_acct_not_app_spt_old t) b
    on(a.user_id=b.user_id and a.acct_item_id=b.acct_item_id)
    when matched then update set
    a.receivable_fee = b.receivable_fee
    when not matched then insert
    (
     a.user_id,
     a.acct_item_id,
     a.receivable_fee,
     a.acct_cycle_id
    )
    values
    (
     b.user_id,
     b.acct_item_id,
     b.receivable_fee,
     b.acct_cycle_id
    );