[Oracle]Merge语句
程序员文章站
2022-08-08 09:41:05
merge的语法如下:
merge [hint] into [schema .] table [t_alias] using [schema .]
{ table | v...
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需要引起怀疑,不太应该。