Oracle中merge into的使用

標籤：blog   http   io   os   ar   使用   sp   strong   資料   

該命令使用一條語句從一個或者多個資料來源中完成對錶的更新和插入資料. ORACLE 9i 中，使用此命令必須同時指定UPDATE 和INSERT 關鍵詞,ORACLE 10g 做了如下改動。

1，insert 和update是可選的 2，UPDATE 和INSERT 後面可以跟WHERE 子句 3,在ON條件中可以使用常量來insert 所有的行到目標表中，不需要串連到源表和目標表 4,UPDATE 子句後面可以跟delete 來去除一些不需要的行。

舉例：

1. create table PRODUCTS   
2.     (   
3.     PRODUCT_ID INTEGER,   
4.     PRODUCT_NAME VARCHAR2(60),   
5.     CATEGORY VARCHAR2(60)   
6.     );   
7.   
8.     insert into PRODUCTS values (1501, ‘VIVITAR 35MM‘, ‘ELECTRNCS‘);   
9.     insert into PRODUCTS values (1502, ‘OLYMPUS IS50‘, ‘ELECTRNCS‘);   
10.     insert into PRODUCTS values (1600, ‘PLAY GYM‘, ‘TOYS‘);   
11.     insert into PRODUCTS values (1601, ‘LAMAZE‘, ‘TOYS‘);   
12.     insert into PRODUCTS values (1666, ‘HARRY POTTER‘, ‘DVD‘);   
13.     commit;   
14.   
15.     create table NEWPRODUCTS   
16.     (   
17.     PRODUCT_ID INTEGER,   
18.     PRODUCT_NAME VARCHAR2(60),   
19.     CATEGORY VARCHAR2(60)   
20.     );   
21.   
22.     insert into NEWPRODUCTS values (1502, ‘OLYMPUS CAMERA‘, ‘ELECTRNCS‘);   
23.     insert into NEWPRODUCTS values (1601, ‘LAMAZE‘, ‘TOYS‘);   
24.     insert into NEWPRODUCTS values (1666, ‘HARRY POTTER‘, ‘TOYS‘);   
25.     insert into NEWPRODUCTS values (1700, ‘WAIT INTERFACE‘, ‘BOOKS‘);   
26.     commit;   
28. 1，可省略的update 或者insert
29.    MERGE INTO products p   
30.     2 USING newproducts np   
31.     3 ON (p.product_id = np.product_id)   
32.     4 WHEN MATCHED THEN  
33.     5 UPDATE  
34.     6 SET p.product_name = np.product_name,   
35.     7 p.category = np.category;

  使用表newproducts中的product_name 和category欄位來更新表products 中相同product_id的product_name 和category.

2，當條件不滿足的時候把newproducts表中的資料INSERT 到表products中。

1. MERGE INTO products p   
2.     USING newproducts np   
3.      ON (p.product_id = np.product_id)   
4.      WHEN NOT MATCHED THEN  
5.      INSERT  
6.      VALUES (np.product_id, np.product_name,   
7.      np.category);   

3，帶條件的insert 和update

1. MERGE INTO products p   
2.  USING newproducts np   
3.    ON (p.product_id = np.product_id)   
4.    WHEN MATCHED THEN  
5.    UPDATE  
6.    SET p.product_name = np.product_name   
7.    WHERE p.category = np.category;

 insert 和update　都帶有where 字句

 

 

1. MERGE INTO products p   
2.  USING newproducts np   
3.     ON (p.product_id = np.product_id)   
4.     WHEN MATCHED THEN  
5.     UPDATE  
6.      SET p.product_name = np.product_name,   
7.      p.category = np.category   
8.     WHERE p.category = ‘DVD‘  
9.     WHEN NOT MATCHED THEN  
10.      INSERT  
11.      VALUES (np.product_id, np.product_name, np.category)   
12.     WHERE np.category != ‘BOOKS‘  

4,無條件的insert

1. MERGE INTO products p   
2.  USING newproducts np   
3.   ON (1=0)   
4.   WHEN NOT MATCHED THEN  
5.    INSERT  
6.    VALUES (np.product_id, np.product_name, np.category)   
7.    WHERE np.category = ‘BOOKS‘  
   

 

5，delete 子句

１  merge into products p
  2  using newproducts np
  3  on(p.product_id = np.product_id)
  4  when matched then
  5  update
  6  set p.product_name = np.product_name
  7  delete where category = ‘macle1_cate‘;

select *

from products;

 PRODUCT_ID PRODUCT_NAME         CATEGORY
--------------------------------------- -------------------- --------------------
                                   1502 macle22              macle2_cate
                                   1503 macle3                macle2_cate
                                   1504 macle                  macle1_cate
                                   1505 macle5                macle5_cate

1504 中的macle1_cate 滿足delete where，但是不滿足 on 中的條件，所以沒有被刪除。！！！！！！重點

 

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

動機：

想在Oracle中用一條SQL語句直接進行Insert/Update的操作。

說明：

在進行SQL語句編寫時，我們經常會遇到大量的同時進行Insert/Update的語句 ，也就是說當存在記錄時，就更新(Update)，不存在資料時，就插入(Insert)。

實戰：

接下來我們有一個任務，有一個表T，有兩個欄位a,b，我們想在表T中做Insert/Update,如果存在，則更新T中b的值，如果不存在，則插入一條記錄。在Microsoft的SQL文法中，很簡單的一句判斷就可以了，SQL Server中的文法如下：

if exists(select 1 from T where T.a=‘1001‘ ) update T set T.b=2 Where T.a=‘1001‘ else insert into T(a,b) values(‘1001‘,2);

以上語句表明當T表中如果存在a=‘1001‘ 的記錄的話，就把b的值設為2，否則就Insert一條a=‘100‘，b=2的記錄到T中。

但是接下來在Oracle中就遇到麻煩了，記得在Oracle 9i之後就有一條Merge into 的語句可以同時進行Insert 和Update的嗎，Merge的文法如下：

MERGE INTO table_name alias1 
USING (table|view|sub_query) alias2
ON (join condition) 
WHEN MATCHED THEN 
    UPDATE table_name 
    SET col1 = col_val1, 
        col2     = col2_val 
WHEN NOT MATCHED THEN 
    INSERT (column_list) VALUES (column_values); 

上面的文法大家應該都容易懂吧，那我們按照以上的邏輯再寫一次。

MERGE INTO T T1
USING (SELECT a,b FROM T WHERE t.a=‘1001‘) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
  UPDATE SET T1.b = 2
WHEN NOT MATCHED THEN 
  INSERT (a,b) VALUES(‘1001‘,2);

以上的語句貌似很對是吧，實際上，該語句只能進行更新，而無法進行Insert，錯誤在哪裡呢？

其實在Oracle中Merge語句原先是用來進行整表的更新用的，也就是ETL工具比較常用的文法，重點是在Using上。

用中文來解釋Merge文法，就是：

在alias2中Select出來的資料，每一條都跟alias1進行 ON (join condition)的比較，如果匹配，就進行更新的操作(Update),如果不匹配，就進行插入操作(Insert)。

因此，嚴格意義上講，”在一個同時存在Insert和Update文法的Merge語句中，總共Insert/Update的記錄數，就是Using語句中alias2的記錄數。”

以上這句話也就很好的解釋了在上面寫的語句為何只能進行Update，而不能進行Insert了，因為都Select不到資料，如何能進行Insert呢:)

接下來要改成正確的語句就容易多了，如下：

MERGE INTO T T1
USING  ( SELECT ‘1001‘ AS a,2 AS b FROM  dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
  UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN 
  INSERT (a,b) VALUES(T2.a,T2.b);

查詢結果，OK!

注意：

如果不懂Merge語句的原理，Merge語句是一條比較危險的語句，特別是在您只想更新一條記錄的時候，因為不經意間，你可能就把整表的資料都Update了一遍.....汗!!!

我曾經犯過的一個錯誤如下所示，大家看出來是什麼問題了嗎？

MERGE INTO T T1
USING (SELECT Count(*) cnt FROM T WHERE T.a=‘1001‘) T2
ON (T2.cnt>0)
WHEN MATCHED THEN
  UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN 
  INSERT (a,b) VALUES(T2.a,T2.b);

Oracle中merge into的使用