merge在oracle用以从另一张表中取数据插入到一张表中如果两张表中记录符合某些条件则替换如果不符合则插入下面的例子是把mergetest中记录更新到newmergetest如果两张表的id相同则更新newmergetest否则插入
首先建两张测试表
Sql代码
create table mergetest(id number primary keyname varchar());
create table newmergetest(id number primary keyname varchar());
往两张表中插入数据
Sql代码
insert into newmergetest values(first);
insert into newmergetest values(second);
insert into mergetest values(one)
insert into mergetest values(two);
insert into mergetest values(three);
运行更新sql语句
Sql代码
MERGE INTO newmergetest m
using (select * from mergetest) t
ON (mid = tid)
WHEN NOT MATCHED THEN
INSERT VALUES (tidtname)
WHEN MATCHED THEN
UPDATE SET mname = tname
执行完后newmergetest中的记录将和mergetest中的一致不会因为违反主键约束而更新失败
