首页 > 代码库 > [Oracle] Merge语句
[Oracle] Merge语句
Merge的语法如下:
MERGE [hint] INTO [schema .] table [t_alias] USING [schema .] { table | view | subquery } [t_alias] ON ( condition ) WHEN MATCHED THEN merge_update_clause WHEN NOT MATCHED THEN merge_insert_clause;MERGE是什么,如何使用呢?让我们先看一个简单的需求:
需求是,从T1表更新数据到T2表中,如果T2表的NAME 在T1表中已存在,就将MONEY累加,如果不存在,将T1表的记录插入到T2表中。
大家知道,在等价的情况下,一定需要至少两条语句,一条为UPDATE,一条为INSERT,而且语句中必须要与判断的逻辑,或者写在过程中,如果是单条语句,就要写全条件,
写在UPDATE和INSERT的语句中,显的比较麻烦而且容易出错。如果了解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的四大灵活之处
上面讲了Merge的语法和基本用法,事实上Merge可以非常灵活。
1.UPDATE和INSERT动作可只出现其一(9I必须同时出现!)
--我们可选择仅仅UPDATE目标表 MERGE INTO T2 USING T1 ON (T1.NAME=T2.NAME) WHEN MATCHED THEN UPDATE SET T2.MONEY=T1.MONEY+T2.MONEY; --也可选择仅仅INSERT目标表而不做任何UPDATE动作 MERGE INTO T2 USING T1 ON (T1.NAME=T2.NAME) WHEN NOT MATCHED THEN INSERT VALUES (T1.NAME,T1.MONEY);2.可对MERGE语句加条件
MERGE INTO T2 USING T1 ON (T1.NAME=T2.NAME) WHEN MATCHED THEN UPDATE SET T2.MONEY=T1.MONEY+T2.MONEY WHERE T1.NAME='A';3.可用DELETE子句清除行
/* 在这种情况下,首先是要先满足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');4.可采用无条件方式Insert
/* 方法很简单,在语法ON关键字处写上恒不等条件(如1=2)后,MATCHED语句的INSERT就变为无条件INSERT了,具体如下 */ MERGE INTO T2 USING T1 ON (1=2) WHEN NOT MATCHED THEN INSERT VALUES (T1.NAME,T1.MONEY);
Merge的误区
1. 不能更新ON子句引用的列MERGE INTO T2 USING T1 ON (T1.NAME=T2.NAME) WHEN MATCHED THEN UPDATE SET T2.NAME=T1.NAME; ORA-38104: 无法更新 ON 子句中引用的列: "T2"."NAME"2. DELETE子句的WHERE顺序必须最后
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') WHERE T1.NAME='A'; ORA-00933: SQL 命令未正确结束3.DELETE 子句只可以删除目标表,而无法删除源表
/* 这里需要引起注意,无论DELETE WHERE (T2.NAME = 'A' )这个写法的T2是否改写为T1,效果都一样,都是对目标表进行删除! */ SELECT * FROM T1; NAME MONEY -------------------- ---------- A 10 B 20 SELECT * FROM T2; NAME MONEY -------------------- ---------- A 30 C 20 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' ); SELECT * FROM T1; NAME MONEY -------------------- ---------- A 10 B 20 SELECT * FROM T2; NAME MONEY -------------------- ---------- C 20
4.更新同一张表的数据,需担心USING的空值
SELECT * FROM T2; NAME MONEY -------------------- ---------- A 30 C 20 /* 需求为对T2表进行自我更新,如果在T2表中发现NAME=D的记录,就将该记录的MONEY字段更新为100,如果NAME=D的记录不存在, 则自动增加,NAME=D并且MONEY=100的记录。根据语法完成如下代码: */ MERGE INTO T2 USING (select * from t2 where NAME='D') T ON (T.NAME=T2.NAME) WHEN MATCHED THEN UPDATE SET T2.MONEY=100 WHEN NOT MATCHED THEN INSERT VALUES ('D',200); --但是查询发现,本来T表应该因为NAME=D不存在而要增加记录,但是实际却根本无变化。 SQL> SELECT * FROM T2; NAME MONEY ------------------------------------------------------- A 30 C 20 /* 原来是因为此时select * from t2 where NAME='D'为NULL,所以出现了无法插入的情况, 我们可以利用COUNT(*)的值不会为空的特点来等价改造,具体如下: */ MERGE INTO T2 USING (select COUNT(*) CNT from t2 where NAME='D') T ON (T.CNT<>0) WHEN MATCHED THEN UPDATE SET T2.MONEY=100 WHEN NOT MATCHED THEN INSERT VALUES ('D',100); SQL> SELECT * FROM T2; NAME MONEY ------------------------------- A 30 C 20 D 1005. 必须要在源表中获得一组稳定的行
---构造数据,请注意这里多插入一条A记录,就产生了ORA-30926错误 INSERT INTO T1 VALUES ('A',30); COMMIT; ---此时继续执行如下 MERGE INTO T2 USING T1 ON (T1.NAME=T2.NAME) WHEN MATCHED THEN UPDATE SET T2.MONEY=T1.MONEY+T2.MONEY; ORA-30926: 无法在源表中获得一组稳定的行 /* oracle中的merge语句应该保证on中的条件的唯一性,T1.NAME=T2.NAME的时候,T1表记录对应到了T2表的两条记录,所以就出错了。 解决方法很简单,比如我们可以对T1表和T2表的关联字段建主还键,这样基本上就不可能出现这样的问题,而且一般而言,MERGE语句的关联字段互相有主键, MERGE的效率将比较高!或者是将T1表的ID列做一个聚合,这样归并成单条,也能避免此类错误。如: */ MERGE INTO T2 USING (select NAME,SUM(MONEY) AS MONEY FROM T1 GROUP BY NAME)T1 ON (T1.NAME=T2.NAME) WHEN MATCHED THEN UPDATE SET T2.MONEY=T1.MONEY+T2.MONEY; --正常情况下,一般出现重复的NAME需要引起怀疑,不太应该。
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。