語法：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;
構(gòu)建測試數(shù)據(jù)表
create table tj_test
(id number,
name varchar2(20),
age number)
向表中插入數(shù)據(jù)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 232 kk 223 joe 27
構(gòu)建另一新表
create table tj_test1 as select * from tj_test where 1=0插入一筆數(shù)據(jù)insert into tj_test1
values
(1,'jlk',23)select * from tj_test1
1 jkl 23 --注重，這裡的NAME字段中的值是jkl使用MERGE,實(shí)現(xiàn)有則更新，無則插入
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表(對比原來表中的數(shù)據(jù)，更新了ID＝1 ROW中字段NAME同時(shí)，多出兩筆新數(shù)據(jù))select * from tj_test11 jan 23 --這裡的原有jkl值被更新3 joe 27 --原來表中沒有的插入2 kk 22 --原來表中沒有的插入Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1480596
