欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

oracle merge into的用法实例讲解

程序员文章站 2022-10-27 14:53:31
对一张表,在不同的条件下实现不同的操作(update/insert),在 oracle 中可以用 merge into /////////////// 有一个表t,有两个字段a、b,我们想在表t中做...

对一张表,在不同的条件下实现不同的操作(update/insert),在 oracle 中可以用 merge into

///////////////

有一个表t,有两个字段a、b,我们想在表t中做insert/update,如果条件满足,则更新t中b的值,否则在t中插入一条记录。在microsoft的sql语法中,很简单的一句判断就可以了,sql server中的语法如下:  

if exists(select 1 from t where t.a='1001' )
    update t set t.b=2 where t.a='1001' 
else 
    insert into t(a,b) values('1001',2); 

oracle中,要实现相同的功能,要用到merge into来实现(oracle 9i引入的功能),其语法如下:

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 = col_val2 
when not matched then 
    insert (column_list) values (column_values); 

  严格意义上讲,”在一个同时存在insert和update语法的merge语句中,总共insert/update的记录数,就是using语句中alias2的记录数”。所以,要实现上面的功能,可以这样写:

merge into t t1
using (select '1001' as a,2 as b from dual) t2
on ( t1.a=t2.a)
when matched then
    update set t1.b = t2.b
when not matched then 
    insert (a,b) values(t2.a,t2.b);

///////////////////////////////////

使用例子:

create table test (id integer,value varchar2(255) );   
insert into test values (1, 'test1');   
insert into test values (2, 'test2');   

我们想插入一条数据 {id=2,name=’newtest2’} 那么可以这么写

merge into  test t1
using (select '2' as a from dual) t2 on (t1.id=t2.id)
when matched then update set t1.name='newtest2'
when not matched then  insert (t1.id, t1.name) values ('1', 'newtest2'); 

如果id为2的数据存在那么 update,如果不存在insert

注意事项:

merge into的原理是,从using 搜出来的结果逐条与on条件匹配,然后决定是update还是insert。 当using后面的sql没有查询到数据的时候,merge into语句是不会执行update和insert操作的。

所以要想让merge into正常运行,要保证using 后面的select有数据,个人喜欢使用dual表作为using后的表,方便自己控制。