oracle Merge Into 用法

在开发中我们经常会碰到这么一个场景，列如用户修改简单的个人基本信息，这个时候就需要判断用户的基本信息是否存在，如果是首次录入不存在就在表中插入一条信息，如果存在就更新表里的信息。
要实现这种这种场景很简单，我们最常用的就是先查询一下用户的基本信息，执行一下select方法，如果数据存在就执行update方法，如果不存在就就执行insert 方法。这是一种解决思路。
当只是很简单的条件判断时，我们也有另外一种数据库层面的解决思路就是merge into ,很多数据库都有这种语句，因为我用的oracle，这里就记录下oracle的用法，从version 9开始就支持的语法。

首先我们有一张表这里随便抽取的一张平时练习的表

image.png

这里先说下Merge into的语法，merge into中文大概意思就是合并操作

MERGE INTO table_name alias1
USING (table | view | sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
  UPDATE table_name SET col1 = col_val1, col2 = col2_val
WHEN NOT MATCHED THEN
  INSERT (column_list) VALUES (column_values);

这个语句很好理解，操作alias1表的数据，其操作数据的来源为 alias2中数据的记录数，通过on括号中的条件进行过滤，当alias1和alias2中的数据通过on条件进行过滤后，如果存在就执行更新操作，如果不存在就执行插入操作。

现在有这么个需求：判断表中是否存在ID为7的数据，如果存在更新名字为小小明，如果不存在，插入一条小明的数据。
假设场景中名字为唯一，那么我们的更新语句就可以这么写

merge into student a
using (select '7' as id from dual) s
on (a.id = s.id)
when matched then
  update set a.student_name = '小明二号'
when not matched then
  insert (id, student_name, fk_class) values ('7', '小明', '2')

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

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

merge into student a
using (select id  from student where id = '7') s
on (a.id = s.id )
when matched then
  update set a.student_name = '小明二号'
when not matched then
  insert (id, student_name, fk_class) values ('7', '小明', '2')

这么写的话是不会有插入操作的，因为表中并没有id为7的数据，如果把id 改为6，因为表中有查询出的数据能成功执行update

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

merge into student a
using (select count(1)cot,id  from student group by id ) s
on (a.id = s.id and cot > 0)
when matched then
  update set a.student_name = '小明二号'
when not matched then
  insert (id, student_name, fk_class) values ('7', '小明', '2')

这么写的话可以看出明显的错误，只要是id相等且cot大于0，那么查询出的都大于0，会执行全表更新

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

merge into student a
using (select '7' as id from dual) s
on (a.id = s.id)
when matched then
  update set a.id = '7'
when not matched then
  insert (id, student_name, fk_class) values ('7', '小明', '2');

image.png