MERGE用法

MERGEMERGE是什么，如何使用呢？先看一个简单的需求如下:从T1表更新数据到T2表中，如果T2表的NAME 在T1表中已存在，就将MONEY累加，如果不存在，将T1表的记录插入到T2表中。DROP TABLE T1;CREATE TABLE T1 (NAME VARCHAR2(20),MONEY NUMBER);INSERT INTO T1 VALUES ('A',10);INSERT INTO T1 VALUES ('B',20);DROP TABLE T2;CREATE TABLE T2 (NAME VARCHAR2(20),MONEY NUMBER);INSERT INTO T2 VALUES ('A',30);INSERMERGE
MERGE是什么，如何使用呢？先看一个简单的需求如下:
从T1表更新数据到T2表中，如果T2表的NAME 在T1表中已存在，就将MONEY累加，如果不存在，将T1表的记录插入到T2表中。
DROP TABLE T1;
CREATE TABLE T1 (NAME VARCHAR2(20),MONEY NUMBER);
INSERT INTO T1 VALUES ('A',10);
INSERT INTO T1 VALUES ('B',20);
DROP TABLE T2;
CREATE TABLE T2 (NAME VARCHAR2(20),MONEY NUMBER);
INSERT INTO T2 VALUES ('A',30);
INSERT INTO T2 VALUES ('C',20);
COMMIT;
大家知道，一般逻辑思路，该需要至少要UPDATE和INSERT两条SQL才能完成，如考虑在PL/SQL中用纯编程语言思路实现，则必须要考虑判断的逻辑，这样显得更麻烦了。而MERGE语句可直接用单条SQL简洁明快的实现了此业务逻辑，具体如下：
MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=T1.MONEY+T2.MONEY
WHEN NOT MATCHED THEN
INSERT
VALUES (T1.NAME,T1.MONEY);
    此外MERGE语句同样可以分析执行计划，具体如下：
SQL> explain plan for
  2  MERGE INTO T2
  3  USING T1
  4  ON (T1.NAME=T2.NAME)
  5  WHEN MATCHED THEN
  6  UPDATE
  7  SET T2.MONEY=T1.MONEY+T2.MONEY
  8  WHEN NOT MATCHED THEN
  9  INSERT
 10  VALUES (T1.NAME,T1.MONEY);
Explained
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2414655244
----------------------------------------------------------------------------
| Id  | Operation                 | Name |  Rows  | Bytes  |  Cost (%CPU)| Time   |
----------------------------------------------------------------------------
|   0 | MERGE STATEMENT         |      |     2 |   152  |     7  (15)| 00:00:01 |
|   1 |  MERGE                  | T2   |       |        |           |        |
|   2 |   VIEW                  |      |       |        |           |        |
|*  3 |    HASH JOIN OUTER       |      |     2 |   124  |    7  (15) | 00:00:01 |
|   4 |     TABLE ACCESS FUL L     | T1   |     2 |     50  |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL     | T2    |     2 |    74  |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."NAME"="T2"."NAME"(+))
Note
-----
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
 - dynamic sampling used for this statement
21   ows selected
 
1.2 MERGE语法简介
语法如下：
MERGE [hint] INTO [schema .] table [t_alias] USING [schema .]{ table | view | subquery } [t_alias] ON ( condition )WHEN MATCHED THEN merge_update_clauseWHEN NOT MATCHED THEN merge_insert_clause;
 
1.3  Oracle10g中MERGE的完善
在Oracle10g以后，Oracle的MERGE发生了改变
1.4  UPDATE和INSERT动作可只出现其一
可选择仅UPDATE目标表
SQL> MERGE INTO T2
  2  USING T1
  3  ON (T1.NAME=T2.NAME)
  4  WHEN MATCHED THEN
  5  UPDATE
  6  SET T2.MONEY=T1.MONEY+T2.MONEY;
Done
 
也可选择仅仅INSERT目标表而不做任何UPDATE动作
SQL> MERGE INTO T2
  2  USING T1
  3  ON (T1.NAME=T2.NAME)
  4  WHEN NOT MATCHED THEN
  5  INSERT
  6  VALUES (T1.NAME,T1.MONEY);
Done
   注：Oracle9i必须同时出现INSERT和UPDATE操作。
1.5  可对MERGE语句加where条件
SQL> MERGE INTO T2
  2  USING T1
  3  ON (T1.NAME=T2.NAME)
  4  WHEN MATCHED THEN
  5  UPDATE
  6  SET T2.MONEY=T1.MONEY+T2.MONEY
  7  WHERE T1.NAME='A' ---此处表示对MERGE的条件进行过滤
  8  ;
Done
 
1.6  可用DELETE子句清除行（必须同时满足on后的条件和delete where后的条件才有效）
在这种情况下，首先是要先满足T1.NAME=T2.NAME的记录，如果T2.NAME=’A’并不满足T1.NAME=T2.NAME过滤出的记录集，那这个DELETE是不会生效的，在满足的条件下，可以删除目标表的记录。
MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=T1.MONEY+T2.MONEY
DELETE WHERE (T2.NAME = 'A')
;
1.7可采用无条件方式Insert
方法很简单，在语法ON关键字处写上恒不等条件（如1=2）后，MATCHED语句的INSERT就变为无条件INSERT了，同于INSERT...SELECT的写法，具体如下
SQL> MERGE INTO T2
  2  USING T1
  3  ON (1=2)
  4  WHEN NOT MATCHED THEN
  5  INSERT
  6  VALUES (T1.NAME,T1.MONEY);
Done
  
2    MERGE误区探索
2.3  无法在源表中获得一组稳定的行（目标表的一条记录对应源表的多条记录）
MERGE INTO T2 USING T1 ON．．．的MERGE表达式中，如果一条T2记录被连接到多条T1记录，就产生了ORA-30926错误。构造T1,T2表进行试验如下，此次T1表中增加了('A',30)的记录，如下：
DROP TABLE T1;
CREATE TABLE T1 (NAME VARCHAR2(20),MONEY NUMBER);
INSERT INTO T1 VALUES ('A',10);
INSERT INTO T1 VALUES ('A',30);
INSERT INTO T1 VALUES ('B',20);
DROP TABLE T2;
CREATE TABLE T2 (NAME VARCHAR2(20),MONEY NUMBER);
INSERT INTO T2 VALUES ('A',30);
INSERT INTO T2 VALUES ('C',20);
COMMIT
 此时继续执行如下
SQL> MERGE INTO T2
  2  USING T1
  3  ON (T1.NAME=T2.NAME)
  4  WHEN MATCHED THEN
  5  UPDATE
  6  SET T2.MONEY=T1.MONEY+T2.MONEY
  7  ;
ORA-30926: 无法在源表中获得一组稳定的行
 Oracle中的merge语句应该保证on中的条件的唯一性，T1.NAME=’A’时，T2表记录对应到了T1表的两条记录，所以就出错了。
 解决方法很简单，可对T1表和T2表的关联字段建主键，这样基本上就不可能出现这样的问题，而且一般而言，MERGE语句的关联字段互相有主键，MERGE的效率将比较高！
 或者是将T1表的ID列做一个聚合，这样归并成单条，也能避免此类错误。如：
SQL> MERGE INTO T2
  2  USING (SELECT NAME,SUM(MONEY) AS MONEY FROM T1 GROUP BY NAME)T1
  3  ON (T1.NAME=T2.NAME)
  4  WHEN MATCHED THEN
  5  UPDATE
  6  SET T2.MONEY=T1.MONEY+T2.MONEY
  7  ;
Done
但是这样的改造需要注意，因为有可能改变了最终的需求。此外需要引起注意的是，MERGE INTO T2 USING T1 ON．．．的MERGE表达式中，如果反过来，一条T1记录被连接到多条T2记录，是可以导致多条T2记录都被更新而不会出错！继续构造T1,T2表进行试验如下，此次是在T2表中增加了('A',40)的记录，如下：
DROP TABLE T1;
CREATE TABLE T1 (NAME VARCHAR2(20),MONEY NUMBER);
INSERT INTO T1 VALUES ('A',10);
INSERT INTO T1 VALUES ('B',20);
DROP TABLE T2;
CREATE TABLE T2 (NAME VARCHAR2(20),MONEY NUMBER);
INSERT INTO T2 VALUES ('A',30);
INSERT INTO T2 VALUES ('A',40);
INSERT INTO T2 VALUES ('C',20);
COMMIT
   此时继续执行如下，发现执行可以成功并没有报无法在源表中获得一组稳定的行的ORA-30926错误
SQL>  MERGE INTO T2
  2      USING T1
  3      ON (T1.NAME=T2.NAME)
  4      WHEN MATCHED THEN
  5      UPDATE
  6      SET T2.MONEY=T1.MONEY+T2.MONEY
  7      ;
Done
SQL> COMMIT;
Commit complete
   查看T2表，发现T2表中NAME=A的2条记录都被更新了
SQL> SELECT * FROM T2;
NAME                      MONEY
-------------------- ----------------------------------
A                            40
A                            50
C                            20
 
2.4  DELETE子句的WHERE顺序必须最后
SQL> MERGE INTO T2
  2  USING T1
  3  ON (T1.NAME=T2.NAME)
  4  WHEN MATCHED THEN
  5  UPDATE
  6  SET T2.MONEY=T1.MONEY+T2.MONEY
  7  DELETE WHERE (T2.NAME = 'A')
  8  WHERE T1.NAME='A';
/
ORA-00933: SQL 命令未正确结束
改为如下即可
SQL> MERGE INTO T2
  2  USING T1
  3  ON (T1.NAME=T2.NAME)
  4  WHEN MATCHED THEN
  5  UPDATE
  6  SET T2.MONEY=T1.MONEY+T2.MONEY
  7  WHERE T1.NAME='A'
  8  DELETE WHERE (T2.NAME = 'A')
  9  ;
Done
    注：只要是MERGE语句，UPDATE和DELETE两者必须要出现其一，所以上面的脚本是不能省略UPDATE而只做DELETE的。另外WHERE (T2.NAME = 'A')的括号可以省略。
2.5  DELETE 子句只可以删除目标表，而无法删除源表
    这里需要引起注意，无论DELETE WHERE (T2.NAME = 'A' )这个写法的T2是否改写为T1，效果都一样，都是对目标表进行删除！
SQL> SELECT * FROM T1;
NAME                    MONEY
-------------------- ------------------------------
A                            10
B                            20
SQL> SELECT * FROM T2;
NAME                      MONEY
-------------------- --------------------------------
A                            30
C                            20
SQL> MERGE INTO T2
  2  USING T1
  3  ON (T1.NAME=T2.NAME)
  4  WHEN MATCHED THEN
  5  UPDATE
  6  SET T2.MONEY=T1.MONEY+T2.MONEY
  7  DELETE WHERE (T2.NAME = 'A' )
  8  ;
Done
SQL> SELECT * FROM T1;
NAME                      MONEY
-------------------------------------------------------
A                            10
B                            20
SQL> SELECT * FROM T2;
NAME                      MONEY
-------------------- ---------------------------------
C                            20
可以看出目标表的A记录被删除了，但是如果把DELETE WHERE (T2.NAME = 'A' )修改为DELETE WHERE (T1.NAME = 'A' )，是否就会把源表的T1记录给删除了呢？试验如下：
Rollback complete
SQL> MERGE INTO T2
  2  USING T1
  3  ON (T1.NAME=T2.NAME)
  4  WHEN MATCHED THEN
  5  UPDATE
  6  SET T2.MONEY=T1.MONEY+T2.MONEY
  7  DELETE WHERE (T2.NAME = 'A' )
  8  ;
Done
SQL> SELECT * FROM T1;
NAME                      MONEY
-------------------- ---------------------------------
A                            10
B                            20
SQL> SELECT * FROM T2;
NAME                      MONEY
-------------------- --------------------------------
C                            20
    发现其实T1源表的记录根本还是保留着，还只是目标表被删除了。
2.6  更新同一张表的数据,需担心USING的空值
SQL> SELECT * FROM T2;
NAME                      MONEY
-------------------- ---------------------------------
A                            30
C                            20
需求为对T2表进行自我更新，如果在T2表中发现NAME=’D’的记录，就将该记录的MONEY字段更新为100，如果NAME=D的记录不存在，则自动增加NAME=’D’的记录。
    根据语法完成如下代码
SQL> MERGE INTO T2
  2  USING (SELECT * FROM t2 WHERE NAME='D') T
  3  ON (T.NAME=T2.NAME)
  4  WHEN MATCHED THEN
  5  UPDATE
  6  SET T2.MONEY=100
  7  WHEN NOT MATCHED THEN
  8  INSERT
  9  VALUES ('D',200)
 10  ;
Done
但是查询发现，本来T表应该因为NAME=D不存在而要增加记录，但是实际却根本无变化。
SQL> SELECT * FROM T2;
NAME                      MONEY
-------------------------------------------------------
A                            30
C                            20
原因是USING后面必须包含要更新或插入的行。而第一个USING (SELECT * FROM t2 WHERE NAME='D') T 根本没有这一行，可改造如下巧妙实现需求:
SQL> MERGE INTO T2
  2  USING (SELECT COUNT(*) CNT FROM t2 WHERE NAME='D') T
  3  ON (T.CNT<>0)
  4  WHEN MATCHED THEN
  5  UPDATE
  6  SET T2.MONEY=100
  7  WHEN NOT MATCHED THEN
  8  INSERT
  9  VALUES ('D',100)
 10  ;
Done
SQL> SELECT * FROM T2;
NAME                      MONEY
-------------------- -----------------------------------
A                            30
C                            20
D                           100
 
3    MERGE的巧妙运用
案例1：
需求为：将如下TEST记录的ID=1的NAME改为ID=2的NAME的值，把ID=2的NAME改为ID=1的NAME的值。
drop table test;
create table test (id number,name varchar2(20));
insert into test values (1,'a');
insert into test values (2,'b');
COMMIT;
SQL> SELECT * FROM test;
        ID NAME
---------- --------------------
         1 a
         2 b
如果执行如下：
UPDATE TEST SET NAME =(SELECT NAME FROM TEST WHERE ID=2) WHERE ID=1;
此时ID=1的NAME值已改变了，就不可能用如下来更新了
UPDATE TEST SET NAME =(SELECT NAME FROM TEST WHERE ID=1) WHERE ID=2;
如果是过程就很简单了，可以把原先的值先存储起来。但是是否单条SQL一定不行呢？
其实单条SQL是可以解决的，可考虑灵活利用MERGE特性！思路可考虑构造出一个虚拟表T，然后再根据此虚拟T表和真实的TEST表进行MERGE更新，就方便快捷的完成了。构造的虚拟表方法类似如下：
SQL> SELECT 1 id,(SELECT name FROM test WHERE id=2) name FROM DUAL
  2         UNION ALL
  3         SELECT 2,(SELECT name FROM test WHERE id=1) FROM DUAL
  4  ;
        ID NAME
---------- --------------------
         1 b
         2 a
   有了此思路，结合前面所学的MERGE知识，可以通过如下简洁优雅定的代码完成更新。
SQL> MERGE INTO TEST
  2  USING (SELECT 1 id,(SELECT name FROM test WHERE id=2) name FROM DUAL
  3         UNION ALL
  4         SELECT 2,(SELECT name FROM test WHERE id=1) FROM DUAL
  5         ) t
  6  ON (test.id = t.id)
  7  WHEN MATCHED THEN UPDATE set TEST.name = t.name
  8  ;
Done
SQL> SELECT * FROM test;
        ID NAME
---------- --------------------
         1 b
         2 a
注：如果是9I固定需要INSERT，所以需要随便加上如下内容
WHEN NOT MATCHED THEN
INSERT   VALUES (1,'a')
 
本案例用的是MERGE的方法，当然，其中的构造虚拟表也是一个非常重要的思路，如果只是查询出改变后的结果而不是真实的进行更新，就可以不采用MERGE，直接可以采用如下方式取出结果
SQL> rollback;
Rollback complete
SQL>
SQL> WITH T AS
  2  (SELECT 1 id,(SELECT name FROM test WHERE id=2) name FROM DUAL
  3         UNION ALL
  4         SELECT 2,(SELECT name FROM test WHERE id=1) FROM DUAL
  5         )
  6  SELECT test.id,t.name FROM test ,t
  7  WHERE test.id=t.id;
        ID NAME
---------- --------------------
         1 b
         2 a


在这里了解一下with as的语法：


create table t (x number(10), y number(10));
insert into t values (1,110);
insert into t values (2,120);
insert into t values (2,80);
insert into t values (3,150);
insert into t values (3,30);
insert into t values (3,60);
commit;
select * from t;
需求描述
按照x列分组后统计y列的总值，最终目标是选出比y列总值的三分之一大的那些分组统计信息

使用子查询方式实现
最容易想到的方法
SELECT x, SUM (y) AS total_y
     FROM t
   GROUP BY x
   HAVING SUM (y) > (SELECT SUM(y) / 3 FROM t)
   ORDER BY total_y



WITH Clause方法闪亮登场
WITH secooler_sum AS (SELECT x, SUM (y) total_y
                          FROM t
                        GROUP BY x)
  SELECT x, total_y
    FROM secooler_sum
   WHERE total_y > (SELECT SUM (total_y) / 3 FROM secooler_sum)
  ORDER BY total_y

查询语句不是以select开始的，而是以“WITH”关键字开头
可认为在真正进行查询之前预先构造了一个临时表secooler_sum，之后便可多次使用它做进一步的分析和处理
WITH Clause方法的优点
增加了SQL的易读性，如果构造了多个子查询，结构会更清晰；
更重要的是：“一次分析，多次使用”，这也是为什么会提供性能的地方，达到了“少读”的目标
知其所以然
为什么WITH Clause方法会提高效率？通过查看上面两种方法的执行计划便可略知一二
1）使用子查询的执行计划
set autot trace exp

第一种使用子查询的方法T表被扫描了两次，而使用WITH Clause方法，T表仅被扫描一次
这也是为什么在大型数据仓库系统中推荐使用WITH Clause方法进行查询统计的原因，这样可以大大的提高数据分析和查询的效率

另外，观察WITH Clause方法执行计划，其中“SYS_TEMP_XXXX”便是在运行过程中构造的中间统计结果临时表。

案例2
通过MERGE可以得到一个非常有用的思想，就是如下：只要能查出更新后的结果集，就可利用该结果集来更新原表记录，即MERGE+ROWID方式。感谢NEWKID给予的指点，他精于使用此类方法，下文案例3中的复杂MERGE更新例子即来自NEWKID的精彩脚本。
本案例2来源于案例1的延伸，改变了案例1的处理思路，不再采用构造虚拟表T来关联TEST表的方式，而是直接把真实结果用SELECT的方式取出，然后利用这个结果集更新回原表中。
SQL> merge into  test using
  2  (
  3  WITH T AS
  4   (SELECT 1 id,(SELECT name FROM test WHERE id=2) name FROM DUAL
  5          UNION ALL
  6          SELECT 2,(SELECT name FROM test WHERE id=1) FROM DUAL
  7          )
  8   SELECT test.id,test.rowid as rn ,t.name FROM test ,t
  9   WHERE test.id=t.id
10  )      n
 11 on(test.rowid=n.rn)
 12  when matched then  update
 13  set test.name=n.name;
SQL> SELECT * FROM test;
        ID NAME
---------- --------------------
         1 a
         2 b
 
注：直接UPDATE一个子查询的写法也可行，但是却有很多限制，稍微复杂的查询都易出错。此时用MERGE是最好的办法，结合ROWID的方式，可快速准确的利用一个已查询出的结果集来更新自己，是一个非常好的思路的扩展，希望对大家有借鉴。
案例3
在文章的最后，举一个USING里面有复杂的连接、聚合、分析函数的综合性例子来加深读者的印象，从而更深入的理解MERGE的强大功能！脚本选自NEWKID在答网友提问的一次精彩回复，提问需求如下：
declarecursor c1 isSELECT art_no,stock
FROM  tb_fin_art_stockWHERE  run_date=to_date('&日期','yyyymmdd')-1  and art_no in (158756);t_art tb_fin_art_stock.art_no%type;t_stock tb_fin_art_STOCK.stock%type;beginopen c1;loop        fetch c1 into t_art,t_stock;        exit when c1%notfound; update tb_fin_art_stock set stock=t_stock+gor_qty+return_qty-sale_qty+stock_corr+DEL_CORR                 WHERE run_date=to_date('&日期','yyyymmdd')                        and art_no=t_art;        commit;end loop;close c1;end;    以上代码，我想更新tb_fin_art_stock 这个表中某个货号某一天往后所有记录的 stock(库存)字段的值，每天的stock是根据前一天的stock字段的值加进货减销售得出来的。现在只能一天一天更新。我想问的是如何能输入日期范围，比如10号到20号的记录，根据9号更改10号，根据10号再改11号，。。。。以此类推，一次就更新了。
MERGE精彩解决方案如下，有兴趣的读者自行研究，相信必有收获！
MERGE INTO tb_fin_art_stock tUSING (SELECT t.ROWID rid             ,t2.stock+SUM(gor_qty+return_qty-sale_qty+stock_corr+DEL_CORR) OVER(PARTITION BY art_no ORDER BY run_date) AS stock         FROM tb_fin_art_stock t             ,(SELECT art_no,stock FROM tb_fin_art_stock WHERE run_date = lv_start_date-1) t2        WHERE t.run_date BETWEEN lv_start_date AND lv_end_date              AND t.art_no = t2.art_no       ) nUSING (t.ROWID = n.rid)WHEN MATCHED THEN UPDATE SET t.stock = n.stock;
 
T INTO T2 VALUES ('C',20);COMMIT;大家知道，一般逻辑思路，该需要至少要UPDATE和INSERT两条SQL才能完成，如考虑在PL/SQL中用纯编程语言思路实现，则必须要考虑判断的逻辑，这样显得更麻烦了。而MERGE语句可直接用单条SQL简洁明快的实现了此业务逻辑，具体如下：MERGE INTO T2USING T1ON (T1.NAME=T2.NAME)WHEN MATCHED THENUPDATESET T2.MONEY=T1.MONEY+T2.MONEYWHEN NOT MATCHED THENINSERTVALUES (T1.NAME,T1.MONEY);    此外MERGE语句同样可以分析执行计划，具体如下：SQL> explain plan for  2  MERGE INTO T2  3  USING T1  4  ON (T1.NAME=T2.NAME)  5  WHEN MATCHED THEN  6  UPDATE  7  SET T2.MONEY=T1.MONEY+T2.MONEY  8  WHEN NOT MATCHED THEN  9  INSERT 10  VALUES (T1.NAME,T1.MONEY);ExplainedSQL> SELECT * FROM table(dbms_xplan.display);PLAN_TABLE_OUTPUT---------------------------------------------------------------------------Plan hash value: 2414655244----------------------------------------------------------------------------| Id  | Operation                 | Name |  Rows  | Bytes  |  Cost (%CPU)| Time   |----------------------------------------------------------------------------|   0 | MERGE STATEMENT         |      |     2 |   152  |     7  (15)| 00:00:01 ||   1 |  MERGE                  | T2   |       |        |           |        ||   2 |   VIEW                  |      |       |        |           |        ||*  3 |    HASH JOIN OUTER       |      |     2 |   124  |    7  (15) | 00:00:01 ||   4 |     TABLE ACCESS FUL L     | T1   |     2 |     50  |     3   (0)| 00:00:01 ||   5 |     TABLE ACCESS FULL     | T2    |     2 |    74  |     3   (0)| 00:00:01 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - access("T1"."NAME"="T2"."NAME"(+))Note-----PLAN_TABLE_OUTPUT---------------------------------------------------------------------------- - dynamic sampling used for this statement21   ows selected 1.2 MERGE语法简介语法如下：MERGE [hint] INTO [schema .] table [t_alias] USING [schema .]{ table | view | subquery } [t_alias] ON ( condition )WHEN MATCHED THEN merge_update_clauseWHEN NOT MATCHED THEN merge_insert_clause; 1.3  Oracle10g中MERGE的完善在Oracle10g以后，Oracle的MERGE发生了改变1.4  UPDATE和INSERT动作可只出现其一可选择仅UPDATE目标表SQL> MERGE INTO T2  2  USING T1  3  ON (T1.NAME=T2.NAME)  4  WHEN MATCHED THEN  5  UPDATE  6  SET T2.MONEY=T1.MONEY+T2.MONEY;Done 也可选择仅仅INSERT目标表而不做任何UPDATE动作SQL> MERGE INTO T2  2  USING T1  3  ON (T1.NAME=T2.NAME)  4  WHEN NOT MATCHED THEN  5  INSERT  6  VALUES (T1.NAME,T1.MONEY);Done   注：Oracle9i必须同时出现INSERT和UPDATE操作。1.5  可对MERGE语句加where条件SQL> MERGE INTO T2  2  USING T1  3  ON (T1.NAME=T2.NAME)  4  WHEN MATCHED THEN  5  UPDATE  6  SET T2.MONEY=T1.MONEY+T2.MONEY  7  WHERE T1.NAME='A' ---此处表示对MERGE的条件进行过滤  8  ;Done 1.6  可用DELETE子句清除行（必须同时满足on后的条件和delete where后的条件才有效）在这种情况下，首先是要先满足T1.NAME=T2.NAME的记录，如果T2.NAME=’A’并不满足T1.NAME=T2.NAME过滤出的记录集，那这个DELETE是不会生效的，在满足的条件下，可以删除目标表的记录。MERGE INTO T2USING T1ON (T1.NAME=T2.NAME)WHEN MATCHED THENUPDATESET T2.MONEY=T1.MONEY+T2.MONEYDELETE WHERE (T2.NAME = 'A');1.7可采用无条件方式Insert方法很简单，在语法ON关键字处写上恒不等条件（如1=2）后，MATCHED语句的INSERT就变为无条件INSERT了，同于INSERT...SELECT的写法，具体如下SQL> MERGE INTO T2  2  USING T1  3  ON (1=2)  4  WHEN NOT MATCHED THEN  5  INSERT  6  VALUES (T1.NAME,T1.MONEY);Done  2    MERGE误区探索2.3  无法在源表中获得一组稳定的行（目标表的一条记录对应源表的多条记录）MERGE INTO T2 USING T1 ON．．．的MERGE表达式中，如果一条T2记录被连接到多条T1记录，就产生了ORA-30926错误。构造T1,T2表进行试验如下，此次T1表中增加了('A',30)的记录，如下：DROP TABLE T1;CREATE TABLE T1 (NAME VARCHAR2(20),MONEY NUMBER);INSERT INTO T1 VALUES ('A',10);INSERT INTO T1 VALUES ('A',30);INSERT INTO T1 VALUES ('B',20);DROP TABLE T2;CREATE TABLE T2 (NAME VARCHAR2(20),MONEY NUMBER);INSERT INTO T2 VALUES ('A',30);INSERT INTO T2 VALUES ('C',20);COMMIT 此时继续执行如下SQL> MERGE INTO T2  2  USING T1  3  ON (T1.NAME=T2.NAME)  4  WHEN MATCHED THEN  5  UPDATE  6  SET T2.MONEY=T1.MONEY+T2.MONEY  7  ;ORA-30926: 无法在源表中获得一组稳定的行 Oracle中的merge语句应该保证on中的条件的唯一性，T1.NAME=’A’时，T2表记录对应到了T1表的两条记录，所以就出错了。 解决方法很简单，可对T1表和T2表的关联字段建主键，这样基本上就不可能出现这样的问题，而且一般而言，MERGE语句的关联字段互相有主键，MERGE的效率将比较高！ 或者是将T1表的ID列做一个聚合，这样归并成单条，也能避免此类错误。如：SQL> MERGE INTO T2  2  USING (SELECT NAME,SUM(MONEY) AS MONEY FROM T1 GROUP BY NAME)T1  3  ON (T1.NAME=T2.NAME)  4  WHEN MATCHED THEN  5  UPDATE  6  SET T2.MONEY=T1.MONEY+T2.MONEY  7  ;Done但是这样的改造需要注意，因为有可能改变了最终的需求。此外需要引起注意的是，MERGE INTO T2 USING T1 ON．．．的MERGE表达式中，如果反过来，一条T1记录被连接到多条T2记录，是可以导致多条T2记录都被更新而不会出错！继续构造T1,T2表进行试验如下，此次是在T2表中增加了('A',40)的记录，如下：DROP TABLE T1;CREATE TABLE T1 (NAME VARCHAR2(20),MONEY NUMBER);INSERT INTO T1 VALUES ('A',10);INSERT INTO T1 VALUES ('B',20);DROP TABLE T2;CREATE TABLE T2 (NAME VARCHAR2(20),MONEY NUMBER);INSERT INTO T2 VALUES ('A',30);INSERT INTO T2 VALUES ('A',40);INSERT INTO T2 VALUES ('C',20);COMMIT   此时继续执行如下，发现执行可以成功并没有报无法在源表中获得一组稳定的行的ORA-30926错误SQL>  MERGE INTO T2  2      USING T1  3      ON (T1.NAME=T2.NAME)  4      WHEN MATCHED THEN  5      UPDATE  6      SET T2.MONEY=T1.MONEY+T2.MONEY  7      ;DoneSQL> COMMIT;Commit complete   查看T2表，发现T2表中NAME=A的2条记录都被更新了SQL> SELECT * FROM T2;NAME                      MONEY-------------------- ----------------------------------A                            40A                            50C                            20 2.4  DELETE子句的WHERE顺序必须最后SQL> MERGE INTO T2  2  USING T1  3  ON (T1.NAME=T2.NAME)  4  WHEN MATCHED THEN  5  UPDATE  6  SET T2.MONEY=T1.MONEY+T2.MONEY  7  DELETE WHERE (T2.NAME = 'A')  8  WHERE T1.NAME='A';/ORA-00933: SQL 命令未正确结束改为如下即可SQL> MERGE INTO T2  2  USING T1  3  ON (T1.NAME=T2.NAME)  4  WHEN MATCHED THEN  5  UPDATE  6  SET T2.MONEY=T1.MONEY+T2.MONEY  7  WHERE T1.NAME='A'  8  DELETE WHERE (T2.NAME = 'A')  9  ;Done    注：只要是MERGE语句，UPDATE和DELETE两者必须要出现其一，所以上面的脚本是不能省略UPDATE而只做DELETE的。另外WHERE (T2.NAME = 'A')的括号可以省略。2.5  DELETE 子句只可以删除目标表，而无法删除源表    这里需要引起注意，无论DELETE WHERE (T2.NAME = 'A' )这个写法的T2是否改写为T1，效果都一样，都是对目标表进行删除！SQL> SELECT * FROM T1;NAME                    MONEY-------------------- ------------------------------A                            10B                            20SQL> SELECT * FROM T2;NAME                      MONEY-------------------- --------------------------------A                            30C                            20SQL> MERGE INTO T2  2  USING T1  3  ON (T1.NAME=T2.NAME)  4  WHEN MATCHED THEN  5  UPDATE  6  SET T2.MONEY=T1.MONEY+T2.MONEY  7  DELETE WHERE (T2.NAME = 'A' )  8  ;DoneSQL> SELECT * FROM T1;NAME                      MONEY-------------------------------------------------------A                            10B                            20SQL> SELECT * FROM T2;NAME                      MONEY-------------------- ---------------------------------C                            20可以看出目标表的A记录被删除了，但是如果把DELETE WHERE (T2.NAME = 'A' )修改为DELETE WHERE (T1.NAME = 'A' )，是否就会把源表的T1记录给删除了呢？试验如下：Rollback completeSQL> MERGE INTO T2  2  USING T1  3  ON (T1.NAME=T2.NAME)  4  WHEN MATCHED THEN  5  UPDATE  6  SET T2.MONEY=T1.MONEY+T2.MONEY  7  DELETE WHERE (T2.NAME = 'A' )  8  ;DoneSQL> SELECT * FROM T1;NAME                      MONEY-------------------- ---------------------------------A                            10B                            20SQL> SELECT * FROM T2;NAME                      MONEY-------------------- --------------------------------C                            20    发现其实T1源表的记录根本还是保留着，还只是目标表被删除了。2.6  更新同一张表的数据,需担心USING的空值SQL> SELECT * FROM T2;NAME                      MONEY-------------------- ---------------------------------A                            30C                            20需求为对T2表进行自我更新，如果在T2表中发现NAME=’D’的记录，就将该记录的MONEY字段更新为100，如果NAME=D的记录不存在，则自动增加NAME=’D’的记录。    根据语法完成如下代码SQL> MERGE INTO T2  2  USING (SELECT * FROM t2 WHERE NAME='D') T  3  ON (T.NAME=T2.NAME)  4  WHEN MATCHED THEN  5  UPDATE  6  SET T2.MONEY=100  7  WHEN NOT MATCHED THEN  8  INSERT  9  VALUES ('D',200) 10  ;Done但是查询发现，本来T表应该因为NAME=D不存在而要增加记录，但是实际却根本无变化。SQL> SELECT * FROM T2;NAME                      MONEY-------------------------------------------------------A                            30C                            20原因是USING后面必须包含要更新或插入的行。而第一个USING (SELECT * FROM t2 WHERE NAME='D') T 根本没有这一行，可改造如下巧妙实现需求:SQL> MERGE INTO T2  2  USING (SELECT COUNT(*) CNT FROM t2 WHERE NAME='D') T  3  ON (T.CNT<>0)  4  WHEN MATCHED THEN  5  UPDATE  6  SET T2.MONEY=100  7  WHEN NOT MATCHED THEN  8  INSERT  9  VALUES ('D',100) 10  ;DoneSQL> SELECT * FROM T2;NAME                      MONEY-------------------- -----------------------------------A                            30C                            20D                           100 3    MERGE的巧妙运用案例1：需求为：将如下TEST记录的ID=1的NAME改为ID=2的NAME的值，把ID=2的NAME改为ID=1的NAME的值。drop table test;create table test (id number,name varchar2(20));insert into test values (1,'a');insert into test values (2,'b');COMMIT;SQL> SELECT * FROM test;        ID NAME---------- --------------------         1 a         2 b如果执行如下：UPDATE TEST SET NAME =(SELECT NAME FROM TEST WHERE ID=2) WHERE ID=1;此时ID=1的NAME值已改变了，就不可能用如下来更新了UPDATE TEST SET NAME =(SELECT NAME FROM TEST WHERE ID=1) WHERE ID=2;如果是过程就很简单了，可以把原先的值先存储起来。但是是否单条SQL一定不行呢？其实单条SQL是可以解决的，可考虑灵活利用MERGE特性！思路可考虑构造出一个虚拟表T，然后再根据此虚拟T表和真实的TEST表进行MERGE更新，就方便快捷的完成了。构造的虚拟表方法类似如下：SQL> SELECT 1 id,(SELECT name FROM test WHERE id=2) name FROM DUAL  2         UNION ALL  3         SELECT 2,(SELECT name FROM test WHERE id=1) FROM DUAL  4  ;        ID NAME---------- --------------------         1 b         2 a   有了此思路，结合前面所学的MERGE知识，可以通过如下简洁优雅定的代码完成更新。SQL> MERGE INTO TEST  2  USING (SELECT 1 id,(SELECT name FROM test WHERE id=2) name FROM DUAL  3         UNION ALL  4         SELECT 2,(SELECT name FROM test WHERE id=1) FROM DUAL  5         ) t  6  ON (test.id = t.id)  7  WHEN MATCHED THEN UPDATE set TEST.name = t.name  8  ;DoneSQL> SELECT * FROM test;        ID NAME---------- --------------------         1 b         2 a注：如果是9I固定需要INSERT，所以需要随便加上如下内容WHEN NOT MATCHED THENINSERT   VALUES (1,'a') 本案例用的是MERGE的方法，当然，其中的构造虚拟表也是一个非常重要的思路，如果只是查询出改变后的结果而不是真实的进行更新，就可以不采用MERGE，直接可以采用如下方式取出结果SQL> rollback;Rollback completeSQL>SQL> WITH T AS  2  (SELECT 1 id,(SELECT name FROM test WHERE id=2) name FROM DUAL  3         UNION ALL  4         SELECT 2,(SELECT name FROM test WHERE id=1) FROM DUAL  5         )  6  SELECT test.id,t.name FROM test ,t  7  WHERE test.id=t.id;        ID NAME---------- --------------------         1 b         2 a在这里了解一下with as的语法：create table t (x number(10), y number(10)); insert into t values (1,110); insert into t values (2,120); insert into t values (2,80); insert into t values (3,150); insert into t values (3,30); insert into t values (3,60); commit;select * from t; 需求描述 按照x列分组后统计y列的总值，最终目标是选出比y列总值的三分之一大的那些分组统计信息 使用子查询方式实现 最容易想到的方法 SELECT x, SUM (y) AS total_y      FROM t    GROUP BY x    HAVING SUM (y) > (SELECT SUM(y) / 3 FROM t)    ORDER BY total_y WITH Clause方法闪亮登场 WITH secooler_sum AS (SELECT x, SUM (y) total_y                           FROM t                         GROUP BY x)   SELECT x, total_y     FROM secooler_sum    WHERE total_y > (SELECT SUM (total_y) / 3 FROM secooler_sum)   ORDER BY total_y 查询语句不是以select开始的，而是以“WITH”关键字开头 可认为在真正进行查询之前预先构造了一个临时表secooler_sum，之后便可多次使用它做进一步的分析和处理 WITH Clause方法的优点 增加了SQL的易读性，如果构造了多个子查询，结构会更清晰； 更重要的是：“一次分析，多次使用”，这也是为什么会提供性能的地方，达到了“少读”的目标 知其所以然 为什么WITH Clause方法会提高效率？通过查看上面两种方法的执行计划便可略知一二 1）使用子查询的执行计划 set autot trace exp 第一种使用子查询的方法T表被扫描了两次，而使用WITH Clause方法，T表仅被扫描一次 这也是为什么在大型数据仓库系统中推荐使用WITH Clause方法进行查询统计的原因，这样可以大大的提高数据分析和查询的效率 另外，观察WITH Clause方法执行计划，其中“SYS_TEMP_XXXX”便是在运行过程中构造的中间统计结果临时表。 案例2通过MERGE可以得到一个非常有用的思想，就是如下：只要能查出更新后的结果集，就可利用该结果集来更新原表记录，即MERGE+ROWID方式。感谢NEWKID给予的指点，他精于使用此类方法，下文案例3中的复杂MERGE更新例子即来自NEWKID的精彩脚本。本案例2来源于案例1的延伸，改变了案例1的处理思路，不再采用构造虚拟表T来关联TEST表的方式，而是直接把真实结果用SELECT的方式取出，然后利用这个结果集更新回原表中。SQL> merge into  test using  2  (  3  WITH T AS  4   (SELECT 1 id,(SELECT name FROM test WHERE id=2) name FROM DUAL  5          UNION ALL  6          SELECT 2,(SELECT name FROM test WHERE id=1) FROM DUAL  7          )  8   SELECT test.id,test.rowid as rn ,t.name FROM test ,t  9   WHERE test.id=t.id10  )      n 11 on(test.rowid=n.rn) 12  when matched then  update 13  set test.name=n.name;SQL> SELECT * FROM test;        ID NAME---------- --------------------         1 a         2 b 注：直接UPDATE一个子查询的写法也可行，但是却有很多限制，稍微复杂的查询都易出错。此时用MERGE是最好的办法，结合ROWID的方式，可快速准确的利用一个已查询出的结果集来更新自己，是一个非常好的思路的扩展，希望对大家有借鉴。案例3在文章的最后，举一个USING里面有复杂的连接、聚合、分析函数的综合性例子来加深读者的印象，从而更深入的理解MERGE的强大功能！脚本选自NEWKID在答网友提问的一次精彩回复，提问需求如下：declarecursor c1 isSELECT art_no,stockFROM  tb_fin_art_stockWHERE  run_date=to_date('&日期','yyyymmdd')-1  and art_no in (158756);t_art tb_fin_art_stock.art_no%type;t_stock tb_fin_art_STOCK.stock%type;beginopen c1;loop        fetch c1 into t_art,t_stock;        exit when c1%notfound; update tb_fin_art_stock set stock=t_stock+gor_qty+return_qty-sale_qty+stock_corr+DEL_CORR                 WHERE run_date=to_date('&日期','yyyymmdd')                        and art_no=t_art;        commit;end loop;close c1;end;    以上代码，我想更新tb_fin_art_stock 这个表中某个货号某一天往后所有记录的 stock(库存)字段的值，每天的stock是根据前一天的stock字段的值加进货减销售得出来的。现在只能一天一天更新。我想问的是如何能输入日期范围，比如10号到20号的记录，根据9号更改10号，根据10号再改11号，。。。。以此类推，一次就更新了。MERGE精彩解决方案如下，有兴趣的读者自行研究，相信必有收获！MERGE INTO tb_fin_art_stock tUSING (SELECT t.ROWID rid             ,t2.stock+SUM(gor_qty+return_qty-sale_qty+stock_corr+DEL_CORR) OVER(PARTITION BY art_no ORDER BY run_date) AS stock         FROM tb_fin_art_stock t             ,(SELECT art_no,stock FROM tb_fin_art_stock WHERE run_date = lv_start_date-1) t2        WHERE t.run_date BETWEEN lv_start_date AND lv_end_date              AND t.art_no = t2.art_no       ) nUSING (t.ROWID = n.rid)WHEN MATCHED THEN UPDATE SET t.stock = n.stock;