通用SQL开发的利器——MERGE（下）Merge妙用

梁敬彬梁敬弘兄弟出品

往期回顾：

SQL开发的利器——MERGE（上）Merge初探

四．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=1;
如果是过程就很简单了，可以把原先的值先存储起来。但是是否单条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

案例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在答网友提问的一次精彩回复，提问需求如下：

declare
cursor c1 is
SELECT art_no,stock 
FROM  tb_fin_art_stock
WHERE  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;
begin
open 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 t
USING (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
       ) n
USING (t.ROWID = n.rid)
WHEN MATCHED THEN UPDATE SET t.stock = n.stock;

系列回顾

“大白话人工智能” 系列
“数据库拍案惊奇” 系列
“世事洞明皆学问” 系列

三分钟讲述个人感悟——感恩，回馈