oracle merge into 用法详解

merge into能够在一个sql语句中对一个表同时执行insert和update操作。merge命令从一个或多个数据源中选择行来update或insert到一个或多个表。

 

适用于接口表取数，数据上载，判断重复数据进行修改还是插入操作用。

 

语法：

 merge into 表

 using (数据源)

 on 条件

 when matched then

  update操作

 when not matched then

  insert操作

 

实例：

 

merge into OES_ORDERINITDATA_MID oso
using (SELECT t.so_type_code AS ordertype, d.forecast_flang AS orderflag
         FROM table1 t
        INNER JOIN table2 d
           ON t.sale_order_title_id = d.sale_order_title_id
        WHERE t.order_rev_week_number = to_number(v_arrivalweek)
          and t.order_rev_year = v_arrivalyear
          and d.gvs_order_code is not null
          and t.prod_line_code = v_productgroup) oi
on (oso.detailorderno = oi.gvs_order_code)
when matched then
  update set oso.detail_status_code = oi.detail_status_code
when not matched then
  insert
    (oso.ordertype, oso.orderflag)
  values
    (oi.ordertype, oi.orderflag);