Oracle sql merge的用法

(实现多表更新插入)

 

merge into sk_kskc a(目标表) using (select yk_ypcd.ypxh as ypxh,yk_ypcd.ypcd as ypcd,bfbz,zxbz ,lsjg

  from yk_typk ,yk_ypcd where yk_typk.ypxh=yk_ypcd.ypxh) b (数据来源)

on (a.wpxh=b.ypxh and a.wpcd=b.ypcd

 and a.ksdm=414 and a.wplx=0) (目标与来源的条件关联)

when matched then update set a.wpdj = b.lsjg/b.zxbz * b.bfbz (匹配成功进行更新操作)

 

when not matched then insert (a.wpxh,a.wpcd,a.wpdj) values (b.ypxh,b.ypcd, b.lsjg/b.zxbz * b.bfbz)(匹配不成功进行插入操作);

 

 

说明：

满足条件为update set，不满足则为insert values，相互对立，不能交换

 

--1，可省略的update 或者insert

MERGE INTO products p

USING newproducts np

ON (p.product_id = np.product_id)

WHEN MATCHED THEN

  UPDATE SET p.product_name = np.product_name, p.category = np.category;

 

-- 使用表newproducts中的product_name 和category字段来更新表products 中相同product_id的product_name 和category.

 

--2，当条件不满足的时候把newproducts表中的数据INSERT 到表products中。

 

MERGE INTO products p

USING newproducts np

ON (p.product_id = np.product_id)

WHEN NOT MATCHED THEN

  INSERT VALUES (np.product_id, np.product_name, np.category);

 

--3，带条件的insert 和update

 

MERGE INTO products p

USING newproducts np

ON (p.product_id = np.product_id)

WHEN MATCHED THEN

  UPDATE

     SET p.product_name = np.product_name

   WHERE p.category = np.category;

 

insert 和update　都带有where 字句

 

MERGE INTO products p

USING newproducts np

ON (p.product_id = np.product_id)

WHEN MATCHED THEN

  UPDATE

     SET p.product_name = np.product_name, p.category = np.category

   WHERE p.category = 'DVD'

WHEN NOT MATCHED THEN

  INSERT

  VALUES

    (np.product_id, np.product_name, np.category) WHERE np.category != 'BOOKS'

 

--4,无条件的insert

 

MERGE INTO products p USING newproducts np ON

  (1 = 0) WHEN NOT MATCHED THEN INSERT

VALUES

  (np.product_id, np.product_name, np.category) WHERE np.category = 'BOOKS'

 

--5，delete 子句

 

merge into products p using newproducts np on

  (p.product_id = np.product_id) when matched then update set p.product_name = np.product_name delete where category = 'macle1_cate';