语法： merge [hint] into [schema .] table [t_alias] using [schema .] { table | view | subquery } [t_alias] on ( condition ) when matched then merge_update_clause when not matched then merge_insert_clause; oracle 9i中加入了merge 语法： merg
语法：
merge [hint] into [schema .] table [t_alias] using [schema .] { table | view | subquery } [t_alias] on ( condition ) when matched then merge_update_clause when not matched then merge_insert_clause;
oracle 9i中加入了merge
语法：
merge [hint] into [schema .] table [t_alias] using [schema .] { table | view | subquery } [t_alias] on ( condition ) when matched then merge_update_clause when not matched then merge_insert_clause;
构建测试数据表
create table tj_test
(id number,
name varchar2(20),
age number)
向表中插入数据
insert into tj_test
values
(1,'jan',23)
insert into tj_test
values
(2,'kk',22)
insert into tj_test
values
(3,'joe',27)
select * from tj_test
1 jan 23
2 kk 22
3 joe 27
构建另一新表
create table tj_test1 as select * from tj_test where 1=0
插入一笔数据
insert into tj_test1
values
(1,'jlk',23)
select * from tj_test1
1 jkl 23 --注意，，这里的name字段中的值是jkl
使用merge,实现有则更新，无则插入
merge into tj_test1 tt1
using tj_test tt
on (tt1.id=tt.id)
when matched then
update set
tt1.name=tt.name,
tt1.age=tt.age
when not matched then
insert values(
tt.id,
tt.name,
tt.age)
查询tj_test1表(对比原来表中的数据，更新了id=1 row中字段name同时，多出两笔新数据)
select * from tj_test1
1 jan 23 --这里的原有jkl值被更新
3 joe 27 --原来表中没有的插入
2 kk 22 --原来表中没有的插入