oracle Merge Into 用法

ゝ一世哀愁。 2022-11-26 15:55 287阅读 0赞

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

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

![format_png][]

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');

![format_png 1][]

image.png

作者：TryCatch菌  
链接：https://www.jianshu.com/p/8f51ce60d9ba  
来源：简书  
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

[format_png]: /images/20221124/9e26648d14894485b165b26f716f56d4.png
[format_png 1]: /images/20221124/83e55816df5043cd9fa34f152b0ec359.png