語法: 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;
構(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 23
2 kk 22
3 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ù)據(jù))
select * from tj_test1
1 jan 23 --這里的原有jkl值被更新
3 joe 27 --原來表中沒有的插入
2 kk 22 --原來表中沒有的插入
Copyright ? 2019- 91gzw.com 版權(quán)所有 湘ICP備2023023988號-2
違法及侵權(quán)請聯(lián)系:TEL:199 1889 7713 E-MAIL:2724546146@qq.com
本站由北京市萬商天勤律師事務(wù)所王興未律師提供法律服務(wù)