起由

现在有一个表WX_USER_Test，一个Excel。业务要求excel中的两列插入到表中，数据量为11522 条。
项目经理让我把sql写了给他，因为是生产数据的更新，大家都比较慎重。(跨年夜动了三万条数据，涉及三个系统，但仅仅是查询，不足为奇）。

我非常自信，先创建了临时表WX_USER_TMP，将excel中的数据插入，然后使用update批量更新语句。

update

语句一

UPDATE WX_USER_Test T SET (T.RCODE,T.branch) = (SELECT T1.RCODE,T1.BRANCH FROM WX_USER_TMP T1 where T.mobile=T1.mobile)

测试了三条数据没问题就自信满满发给了项目经理。
没想到项目经理Q表示质疑，他觉得set后的子查询并不能保证是同一个手机号被更新。
我为了严谨加了限制条件，
语句二

UPDATE WX_USER_Test T SET (T.RCODE,T.branch) = (SELECT T1.RCODE,T1.BRANCH FROM WX_USER_TMP T1 where T.mobile=T1.mobile)
where T.RCODE is null

Q经理依然表示不相信，于是他前去找了另一个项目经理J（头发略少，事实证明越秃则越强）。
J经理刷刷刷则写了如下的sql，Q发给我时，起初我表示不屑，明明我的简单又短，merge into的略显复杂。
定睛一看，并非如此。
语句三

merge into

merge into WX_USER_Test x
using(SELECT T1.RCODE, T1.BRANCH,t.id
FROM WX_USER_TMP T1 inner join WX_USER_Test T on T.mobile = T1.mobile) y
on(x.id=y.id )
when matched then
update set x.RCODE=y.rcode,x.branch=y.branch

测试结果对比：

update和merge into 都更新11522条记录，
update耗时5.235分钟，加限制条件的执行5.547秒。
merge into 耗时0.234秒钟，相差太大了。

merge语句的语法

MERGE INTO target_table
USING source_table
ON search_condition
WHEN MATCHED THEN
UPDATE SET col1 = value1, col2 = value2,...
WHERE
[DELETE WHERE ]
WHEN NOT MATCHED THEN
INSERT (col1,col2,...)
values(value1,value2,...)
WHERE ;

这个语句的执行过程：
对于source_table中的每一条记录，结合target_table判断该记录是否满足search_condition ：如果满足，执行MATCHED部分的update语句；如果不满足，执行NOT MATCHED部分的insert语句。
其中的upate和insert语句都可以指定where条件，来控制只更新或者插入满足条件的记录。
此外，update语句的部分还可以指定一个可选的带where条件的delete语句。它用来删除这样的记录：符合search_condition，而且在执行update操作之后，各个字段的值满足delete where语句中的条件。update不能更新ON条件子句中引用的列。

官方的例子：

bonuses表记录奖金。原来有销售记录的员工每个人发100。后来公司人力修改了奖金政策，薪水小于等于8000的才会收到奖金，没有销售记录的奖金为其薪水的1%，有销售记录的奖金增加薪水的1%。
在Oracle数据库的使用中，向表中插入数据时，如果待插入的记录表中已经存在，就用新记录的值更新原记录；如果不存在，就插入新记录。这时候，就需要用merge语句。
MERGE语法一步就执行了这些改变。通过merge语句能够避免自己手写好多if判断，程序简洁，更好维护。

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

INSERT INTO bonuses(employee_id)
   (SELECT e.employee_id FROM employees e, orders o
   WHERE e.employee_id = o.sales_rep_id
   GROUP BY e.employee_id);

SELECT * FROM bonuses ORDER BY employee_id;

EMPLOYEE_ID      BONUS
----------- ----------

        153        100

        154        100

        155        100

        156        100

        158        100

        159        100

        160        100

        161        100

        163        100

merge into：

   MERGE INTO bonuses D

   USING (SELECT employee_id, salary FROM employees) S

   ON (D.employee_id = S.employee_id)

   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01

     DELETE WHERE (S.salary > 8000)

   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)

     VALUES (S.employee_id, S.salary*.01)

     WHERE (S.salary <= 8000);

 

SELECT * FROM bonuses ORDER BY employee_id;

 

EMPLOYEE_ID      BONUS

----------- ----------

        153        180

        154        175

        155        170

        159        180

        160        175

        161        170

        164         72

        165         68

        166         64

        167         62

        171         74

        172         73

        173         61

        179         62

update采用的类似nested loop的方式，对更新的每一行，都会对查询的表扫描一次；

merge into这里选择的是hash join，则针对每张表都是做了一次 full table scan，对每张表都只是扫描一次。

Oracle官方建议，
This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements.
merge方法是最简洁，效率最高的方式，在大数据量更新时优先使用这种方式。

后记

诡辩的拼多多砍价99.9%为什么总差一刀，原来小数点后还有6位数？！虽然只是玩笑，但谈及高可用和执行效率，拼的就是99.999…% 中小数点后面的位数。菜鸡与大佬的区别就在于此。道路漫漫，吾将上下而求索。

补充

后续工作中，又学习到了一些知识特此补充。
首先，语句一在上次工作场景中是可用的，因为要更新的两列是之前没有的新增的两列。大部分场景下，语句一是有陷阱的，慎用。具体参考文章oracle多表联合查询更新,ORACLE 两表关联更新三种方式
现需求：参照b表，修改a表，修改条件为两表的fname列内容一致。
按照语句一，会造成a表中有而b表中没有的列被更新为null，造成错误。
优化语句一为正确的：

UPDATE WX_USER_Test T SET (T.RCODE,T.branch) = (SELECT T1.RCODE,T1.BRANCH FROM WX_USER_TMP T1 where T.mobile=T1.mobile) WHERE EXISTS(SELECT 1 FROM WX_USER_TMP T1 WHERE T.mobile= T1.mobile);

博客补充了内联视图更新

UPDATE (select t1.fmoney fmoney1,t2.fmoney fmoney2 from t1,t2 where t1.fname = t2.fname)t set fmoney1 =fmoney2;