merge into 语句

merge into 语句代替insert/update在oracle中,对于大数据关联update ，用merge into 比update 效率高，merge into 只对要更新的表扫描一次
想在oracle中用一条sql语句直接进行insert/update的操作。
在进行sql语句编写时，我们经常会遇到大量的同时进行insert/update的语句 ，也就是说当存在记录时，就更新(update)，不存在数据时，就插入(insert)。
接下来我们有一个任务，有一个表t，有两个字段a,b，我们想在表t中做insert/update,如果存在，则更新t中b的值，如果不存在，则插入一条记录。
在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);

以上语句表明当t表中如果存在a='1001' 的记录的话，就把b的值设为2，否则就insert一条a='100'，b=2的记录到t中。

但是接下来在oracle中就遇到麻烦了，记得在oracle 9i之后就有一条merge into 的语句可以同时进行insert 和update的吗，merge的语法如下：

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);
上面的语法大家应该都容易懂吧，那我们按照以上的逻辑再写一次。

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

以上的语句貌似很对是吧，实际上，该语句只能进行更新，而无法进行insert，错误在哪里呢？
其实在oracle中merge语句原先是用来进行整表的更新用的，也就是etl工具比较常用的语法，重点是在using上。
用中文来解释merge语法，就是：
在alias2中select出来的数据，每一条都跟alias1进行 on (join condition)的比较，如果匹配，就进行更新的操作(update),如果不匹配，就进行插入操作(insert)。
因此，严格意义上讲，在一个同时存在insert和update语法的merge语句中，总共insert/update的记录数，就是using语句中alias2的记录数。”
以上这句话也就很好的解释了在上面写的语句为何只能进行update，而不能进行insert了，因为都select不到数据，如何能进行insert呢:)
接下来要改成正确的语句就容易多了，如下：

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

 

--带条件的insert 和update
merge into products p
using newproducts np
on (p.product_id = np.product_id)
when matched then
update
set p.product_name = np.product_name
where p.category = np.category;

 

--insert 和update　都带有where 字句
merge into products p
using newproducts np
on (p.product_id = np.product_id)
when matched then
update
set p.product_name = np.product_name,
p.category = np.category
where p.category = 'dvd'
when not matched then
insert
values (np.product_id, np.product_name, np.category)
where np.category != 'books'

--无条件的insert
merge into products p
using newproducts np
on (1=0)
when not matched then
insert
values (np.product_id, np.product_name, np.category)
where np.category = 'books'

--delete 子句
merge into products p
using newproducts np
on(p.product_id = np.product_id)
when matched then
update
set p.product_name = np.product_name
delete where category = 'macle1_cate';