2021-05-21 Oracle Merge Into 更新表数据_用merge只做更新操作-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_40600414/article/details/117121607

Merge into

使用版本: Oracle 9 及以上版本

作用:

在平常更新表数据时有时候需要判断存在记录就更新数据,不存在则插入记录,我们需要先判断数据是否存在,需要两条sql, 现在使用merge into 就可以一条sql解决更新数据和插入数据的问题了

语法

 merge into  t1  -- 要更新数据的目标表
 using  t2       -- 要用来更新目标表数据的源表
 on (条件)  -- 目标表与源表的查询结果
 when matched then update set t1.column1=t2.column2 -- 表1中 存在符合条件的记录 更新语句t1表的字段
 when not matched then insert (t1.id,t1.sc) values(t2.id,t2.sc) --表2中不存在符合条件的记录 执行插入操作 向t1表中插入操作

实际应用

存在学生成绩表 sc (id 主键id, sid 学生编号, score 学生成绩)
学生成绩统计表sc_tj(sid 学生编号,sum 成绩总和 )
现在需要将学生成绩表的每个学生的成绩综合统计到成绩统计表, 现在统计表有的学生成绩是存在的,如果存在的学生记录就将学生成绩表的成绩加到原有记录中,如果没有的记录就插入数据

实现sql如下

merge into sc_tj t1 
using (select sid,sum(score) score from sc group by sid ) t2
on(t1.sid=t2.sid)
when matched then update set sum=t1.sum+t2.score
when not matched then insert (t1.sid,t1.sum) values(t2.sid,t2.score)

注意事项:

用merge的时候大概有3个地方需要注意

如果using中的语句查询不出来数据，是不会执行insert方法的，因为这个语法是根据using 中的查询数据进行判断

2：语句写的时候on 中的条件记得过滤准确，不然可能会执行全表更新

3：on 中的条件不能是更新操作列，不然会报错：ora-38104

merge into sc_tj t1 
using (select sid,sum(score) score from sc group by sid ) t2
on(t1.sid=t2.sid)
when matched then update set sid='111'
when not matched then insert (t1.sid,t1.sum) values(t2.sid,t2.score)

在匹配条件里用了sid 字段，就不能在更新操作时进行修改sid字段

4: on 条件进行匹配需要进行一对一匹配，如果源表t2 中存在会重复匹配的信息，可能会报错，所以在on条件中需要控制 t1中一条不会同时匹配到 t2 的两条数据.例如：

merge into  t1 
using  t2
on(t1.id=t2.id)
when matched then update set t1.name=t2.name
when not matched then insert (id,name) values(t2.id,t2.name)

如果 t2中的id不是唯一主键，存在重复id ，那这样t1中的一条记录可能匹配到t2中的多条记录，他就不知道需要匹配哪一条记录了，所以需要控制源表不能有重复与目标表t1 同一条记录匹配的记录

5：针对第4 注意点，如果源表t2 存在可以与目标表t1重复匹配的数据，是会报错 ora-30926 unable to get a stable set of rows in the sources tables 为了处理这种问题，可以先把源表t2的信息进行一下去重，使 t2中不会存在与t1重复存在的数据。针对第4点,只需要把源表数据按照匹配条件进行一下去重就可以了，改进sql如下：

merge into t1
using (select * from(select t2.*,row_number() over (partition by id order by id) rn from t2 ) where rn=1 ) t2 
on(t1.id=t2.id)
when matched then update set t1.name=t2.name
when not matched then insert (id,name) values(t2.id,t2.name)

t1 t2表是按照 id字段进行匹配，所以只需要把源表 t2 按照id进行一下去重就可以了，按照 row_number() 按照id 分组，并且给每个id 组内的记录排号，每个id对应的记录组只取第一条
select * from ( select t2.*,row_number() over (partition by id order by id) rn from t2 ) where rn=1