ITPub博客

首页 > Linux操作系统 > Linux操作系统 > merge用法你了解多少呢,:)

merge用法你了解多少呢,:)

原创 Linux操作系统 作者:nmgzw 时间:2019-06-26 22:33:07 0 删除 编辑
merge在实现数据抽取合并中有着很多优势,这里简单的做个介绍,所有内容都来自oracle database warhouse guide.

1、在10g之前,常用的就是这样的:

MERGE INTO sales s
USING new_sales n
ON (s.sales_transaction_id = n.sales_transaction_id)
WHEN MATCHED THEN
  UPDATE
     SET s.sales_quantity_sold = s.sales_quantity_sold +
                                 n.sales_quantity_sold,
         s.sales_dollar_amount = s.sales_dollar_amount +
                                 n.sales_dollar_amount
WHEN NOT MATCHED THEN
  INSERT
    (sales_transaction_id, sales_quantity_sold, sales_dollar_amount)
  VALUES
    (n.sales_transcation_id, n.sales_quantity_sold, n.sales_dollar_amount); 

2、在10g中增加了功能,就是你可以只用update或insert部分,而不必两者同时出现,同时可以在update,insert部分新增加where条件。比如类似下面的语句:

a、只有update语句:

 
MERGE
USING Product_Changes S
INTO Products D1 
ON (D1.PROD_ID = S.PROD_ID) 
WHEN MATCHED THEN
  UPDATE 
     SET D1.PROD_STATUS = S.PROD_NEW_STATUS;

b、只有insert语句:

 
MERGE
USING New_Product S 
INTO Products D2 
ON (D2.PROD_ID = S.PROD_ID) 
WHEN NOT MATCHED THEN
  INSERT (PROD_ID, PROD_STATUS) VALUES (S.PROD_ID, S.PROD_NEW_STATUS);

c、带where条件的update语句:

 
MERGE
USING Product_Changes S INTO Products P
ON (P.PROD_ID = S.PROD_ID)
WHEN MATCHED THEN
  UPDATE
     SET P.PROD_LIST_PRICE = S.PROD_NEW_PRICE
   WHERE P.PROD_STATUS  "OBSOLETE" ;

d、带where条件的insert语句:

 
MERGE
USING Product_Changes S INTO Products P
ON (P.PROD_ID = S.PROD_ID)
WHEN MATCHED THEN
  UPDATE
     SET P.PROD_LIST_PRICE = S.PROD_NEW_PRICE
   WHERE P.PROD_STATUS  "OBSOLETE"
WHEN NOT MATCHED THEN
  INSERT
    (PROD_ID, PROD_STATUS, PROD_LIST_PRICE)
  VALUES
    (S.PROD_ID, S.PROD_NEW_STATUS, S.PROD_NEW_PRICE) WHERE S.PROD_STATUS  "OBSOLETE";

3、10g中增加了delete语句部分:

MERGE
USING Product_Changes S INTO Products D
ON (D.PROD_ID = S.PROD_ID)
WHEN MATCHED THEN
  UPDATE
     SET D.PROD_LIST_PRICE = S.PROD_NEW_PRICE,
         D.PROD_STATUS     = S.PROD_NEWSTATUS 
  DELETE WHERE (D.PROD_STATUS = "OBSOLETE")
WHEN NOT MATCHED THEN
  INSERT
    (PROD_ID, PROD_LIST_PRICE, PROD_STATUS)
  VALUES
    (S.PROD_ID, S.PROD_NEW_PRICE, S.PROD_NEW_STATUS);
 

4、无条件插入语句:

 
MERGE
USING New_Product S INTO Products P
ON (1 = 0)
WHEN NOT MATCHED THEN
  INSERT (PROD_ID, PROD_STATUS) VALUES (S.PROD_ID, S.PROD_NEW_STATUS); 

5、在10g之前,如果只需要update的时候,可以采用如下实现(从askto学到的):

 
CREATE TABLE alpha (
   id NUMBER(2) PRIMARY KEY,
   NAME VARCHAR2(100) UNIQUE,
   status CHAR(1)
);

CREATE TABLE beta (
   networkid NUMBER(2),
   NAME VARCHAR2(100),
   PRIMARY KEY (NAME, networkid)
);

INSERT INTO alpha VALUES (1, 'Jim', 'A');
INSERT INTO alpha VALUES (2, 'Eric', 'A');
INSERT INTO alpha VALUES (3, 'Ryan', 'A');
COMMIT;

INSERT INTO beta VALUES (10, 'Jim');
INSERT INTO beta VALUES (10, 'Eric');
INSERT INTO beta VALUES (20, 'Ryan');
COMMIT;


 merge into alpha a
    using ( select b.*
             from alpha a, beta b
                    where networkid = 10
                      and a.name = b.name ) b
    on ( a.name = b.name )
    when matched then update set status = 'X'
    when not matched then insert (id) values ( null );

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25702/viewspace-464278/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2002-10-18

  • 博文量
    71
  • 访问量
    57486