很实用的一个了。
离职前mark一下,做一些备忘吧。
建表插值
t1. alias t1.val
aa 10
bb 20
t2. alias t2.val
aa 40
cc 30
# 常规理解很简单,满足条件做update ,新的那么做insert。
merge into t2
using t1
on (t1.alias = t2.alias)
when matched then
update
set t2.val = t2.val + t1.val
when not matched then
insert
values(t1.alias,t1.val)
;
结果:
这边需要注意,Update 的条件视实际需求而定,可能是替换,可能是累加等。Oracle 10g 版本之后可以支持仅insert or update。
下面是一些扩展的用法:
merge 添加where的用法:
# merge也可以添加where条件
# 用法如下:
merge into t2
using t1
on (t1.alias = t2.alias)
when matched then
update
set t2.val = t2.val + t1.val
where t1.alias = 'aa'
;
结果:
merge用于delete删除行
merge into t2
using t1
on (t1.alias = t2.alias)
when matched then
update
set t2.val = t2.val + t1.val
delete where (t1.alias = 'aa')
;
结果:
当delete 和where同时存在时:
# 当delete 和 where 同时存在时,需要先写where 再写 delete
merge into t2
using t1
on (t1.alias = t2.alias)
when matched then
update
set t2.val = t2.val + t1.val
where t1.alias = 'aa'
delete where (t2.alias = 'aa')
;
merge的无条件insert方法:
# merge的无条件insert
merge into t2
using t1
on (1 = 2)
when not matched then
insert
values(t1.alias,t1.val)
;
结果:
对于自我更新的一个扩展:
# 假设场景
# 对于t2表进行自更新。
# 如果在t2中发现alias为dd,那么字段值更新为100。
# 如果没有,那么自动增加dd记录,值为90.
merge into t2
using(select count(*) cnt from t2 where t2.alias = 'dd') t
on (t.cnt <> 0)
when matched then
update
set t2.val = 100
when not matched then
insert
values('dd','90')
;
结果:
还有一些需要注意的点。
Merge into t2 using t1 on (t1.col = t2.col)的时候,当t1表中col有重复值的时候会报错,而t2的col有重复则正常。解决方案为建立主键或者先做聚合,总之要保证on条件的唯一性。其中主键关联,速度效率也更高。
delete只能删除目标表 即 merge的表,对于源表即using的表无效。