oracle中merge into用法解析
oracle中merge into用法解析
merge into語法：
merge into [target-table] a using [source-table sql] b on([conditional expression] and [...]...)
when matched then
[update sql]
when not matched then
[insert sql]
作用:判斷Ｂ表和Ａ表是否滿足on中條件，如果滿足則用b表去更新a表，如果不滿足，則將b表資料插入a表但是有很多可選項，如下:
1.正常模式
2.只update或者只insert
3.帶條件的update或帶條件的insert
4.全插入insert實現
5.帶delete的update(覺得可以用3來實現)
下面一一測試。
測試建以下表:
create table a_merge
(
id number not null,
name varchar2(12) not null,
year number
);
create table b_merge
(
id number not null,
aid number not null,
name varchar2(12) not null,
year number,
city varchar2(12)
);
create table c_merge
(
id number not null,
name varchar2(12) not null,
city varchar2(12) not null
);
commit;
各表的表結構截圖如下所示：
SQL> desc a_merge
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(12)
YEAR NUMBER
SQL> desc b_merge
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
AID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(12)
YEAR NUMBER
CITY VARCHAR2(12)
SQL> desc c_merge
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(12)
CITY NOT NULL VARCHAR2(12)
1.正常模式
先向a_merge和b_merge插入測試資料：
insert into a_merge values(1,'liuwei',20);
insert into a_merge values(2,'zhangbin',21);
insert into a_merge values(3,'fuguo',20);
commit;
insert into b_merge values(1,2,'zhangbin',30,'吉林');
insert into b_merge values(2,4,'yihe',33,'黑龍江');
insert into b_merge (id,aid,name,city) values(3,3,'fuguo','山東');
commit;
此時a_merge和b_merge表中資料如下:
SQL> select * from a_merge;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 20
2 zhangbin 21
3 fuguo 20
SQL> select * from b_merge;
ID AID NAME YEAR CITY
---------- ---------- ------------ ---------- ------------
1 2 zhangbin 30 吉林
2 4 yihe 33 黑龍江
3 3 fuguo 山東
然後再使用merge into用b_merge來更新a_merge中的資料:
merge into a_merge a using (select b.aid,b.name,b.year from b_merge b) c on (a.id=c.aid)
when matched then
update set a.year=c.year
when not matched then
insert(a.id,a.name,a.year) values(c.aid,c.name,c.year);
commit;
此時a_merge中的表資料截圖如下:
SQL> select * from a_merge;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 20
2 zhangbin 30
3 fuguo
4 yihe 33
2.只update模式
首先向b_merge中插入兩個資料，來為了體現出只update沒有insert，必須有一個資料是a中已經存在的
另一個資料時a中不存在的，插入資料語句如下:
insert into b_merge values(4,1,'liuwei',80,'江西');
insert into b_merge values(5,5,'tiantian',23,'河南');
commit;
此時a_merge和b_merge表資料截圖如下:
SQL> select * from a_merge;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 20
2 zhangbin 30
3 fuguo
4 yihe 33
SQL> select * from b_merge;
ID AID NAME YEAR CITY
---------- ---------- ------------ ---------- ------------
1 2 zhangbin 30 吉林
2 4 yihe 33 黑龍江
3 3 fuguo 山東
4 1 liuwei 80 江西
5 5 tiantian 23 河南
然後再次用b_merge來更新a_merge，但是僅僅update，沒有寫insert部分。
merge into a_merge a using (select b.aid,b.name,b.year from b_merge b) c on(a.id=c.aid)
when matched then
update set a.year=c.year;
commit;
merge完之後a_merge表資料如下:可以發現僅僅更新了aid=1的年齡，沒有插入aid=4的資料
SQL> select * from a_merge;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 80
2 zhangbin 30
3 fuguo
4 yihe 33
3.只insert模式
首先改變b_merge中的一個資料，因為上次測試update時新增的資料沒有插入到a_merge，這次可以用。
update b_merge set year=70 where aid=2;
commit;
此時a_merge和b_merge的表資料截圖如下:
SQL> select * from a_merge;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 80
2 zhangbin 30
3 fuguo
4 yihe 33
SQL> select * from b_merge;
ID AID NAME YEAR CITY
---------- ---------- ------------ ---------- ------------
1 2 zhangbin 70 吉林
2 4 yihe 33 黑龍江
3 3 fuguo 山東
4 1 liuwei 80 江西
5 5 tiantian 23 河南
然後用b_merge來更新a_merge中的資料，此時只寫了insert，沒有寫update:
merge into a_merge a using (select b.aid,b.name,b.year from b_merge b) c on(a.id=c.aid)
when not matched then
insert(a.id,a.name,a.year) values(c.aid,c.name,c.year);
commit;
此時a_merge的表資料截圖如下:
SQL> select * from a_merge;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 80
2 zhangbin 30
3 fuguo
4 yihe 33
5 tiantian 23
4.帶where條件的insert和update。
我們在on中進行完條件匹配之後，還可以在後面的insert和update中對on篩選出來的記錄再做一次條件判斷，用來控制哪些要更新，哪些要插入。
測試資料的sql程式碼如下，我們在b_merge修改了兩個人名，並且增加了兩個人員資訊，但是他們來自的省份不同，所以我們可以透過新增省份條件來控制哪些能修改，哪些能插入:
update b_merge set name='yihe++' where id=2;
update b_merge set name='liuwei++' where id=4;
insert into b_merge values(6,6,'ningqin',23,'江西');
insert into b_merge values(7,7,'bing',24,'吉安');
commit;
SQL> select * from a_merge;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 80
2 zhangbin 30
3 fuguo
4 yihe 33
5 tiantian 23
SQL> select * from b_merge;
ID AID NAME YEAR CITY
---------- ---------- ------------ ---------- ------------
1 2 zhangbin 70 吉林
2 4 yihe++ 33 黑龍江
3 3 fuguo 山東
4 1 liuwei++ 80 江西
5 5 tiantian 23 河南
6 6 ningqin 23 江西
7 7 bing 24 吉安
然後再用b_merge去更新a_merge，但是分別在insert和update後面新增了條件限制，控制資料的更新和插入:
merge into a_merge a using (select b.aid,b.name,b.year,b.city from b_merge b) c
on(a.id=c.aid)
when matched then
update set a.name=c.name where c.city != '江西'
when not matched then
insert(a.id,a.name,a.year) values(c.aid,c.name,c.year) where c.city='江西';
commit;
此時a_merge如下:
SQL> select * from a_merge;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 80
2 zhangbin 30
3 fuguo
4 yihe++ 33
5 tiantian 23
6 ningqin 23
已選擇6行。
5.無條件的insert。
有時我們需要將一張表中所有的資料插入到另外一張表，此時就可以新增常量過濾謂詞來實現，讓其只滿足匹配和不匹配，這樣就只有update或者只有insert。這裡我們要無條件全插入，則只需將on中條件設定為永假
即可。用b_merge來更新c_merge程式碼如下:
merge into c_merge c using (select b.aid,b.name,b.city from b_merge b) c on (1=0)
when not matched then
insert(c.id,c.name,c.city) values(b.aid,b.name,b.city);
commit;
a_merge、b_merge、c_merge表在merge之前的資料如下:
SQL> select * from a_merge;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 80
2 zhangbin 30
3 fuguo
4 yihe++ 33
5 tiantian 23
6 ningqin 23
已選擇6行。
SQL> select * from b_merge;
ID AID NAME YEAR CITY
---------- ---------- ------------ ---------- ------------
1 2 zhangbin 70 吉林
2 4 yihe++ 33 黑龍江
3 3 fuguo 山東
4 1 liuwei++ 80 江西
5 5 tiantian 23 河南
6 6 ningqin 23 江西
7 7 bing 24 吉安
已選擇7行。
SQL> select * from c_merge;
ID NAME CITY
---------- ------------ ------------
2 zhangbin 吉林
4 yihe++ 黑龍江
3 fuguo 山東
1 liuwei++ 江西
5 tiantian 河南
6 ningqin 江西
7 bing 吉安
6.帶delete的update
merge提供了在執行資料操作時清除行的選項. 你能夠在when matched then update子句中包含delete子句.
delete子句必須有一個where條件來刪除匹配某些條件的行.匹配delete where條件但不匹配on條件的行不會被從表中刪除.
但我覺得這個跟帶where條件的update差不多，都是控制update，完全可以用帶where條件的update來實現
merge into語法：
merge into [target-table] a using [source-table sql] b on([conditional expression] and [...]...)
when matched then
[update sql]
when not matched then
[insert sql]
作用:判斷Ｂ表和Ａ表是否滿足on中條件，如果滿足則用b表去更新a表，如果不滿足，則將b表資料插入a表但是有很多可選項，如下:
1.正常模式
2.只update或者只insert
3.帶條件的update或帶條件的insert
4.全插入insert實現
5.帶delete的update(覺得可以用3來實現)
下面一一測試。
測試建以下表:
create table a_merge
(
id number not null,
name varchar2(12) not null,
year number
);
create table b_merge
(
id number not null,
aid number not null,
name varchar2(12) not null,
year number,
city varchar2(12)
);
create table c_merge
(
id number not null,
name varchar2(12) not null,
city varchar2(12) not null
);
commit;
各表的表結構截圖如下所示：
SQL> desc a_merge
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(12)
YEAR NUMBER
SQL> desc b_merge
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
AID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(12)
YEAR NUMBER
CITY VARCHAR2(12)
SQL> desc c_merge
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(12)
CITY NOT NULL VARCHAR2(12)
1.正常模式
先向a_merge和b_merge插入測試資料：
insert into a_merge values(1,'liuwei',20);
insert into a_merge values(2,'zhangbin',21);
insert into a_merge values(3,'fuguo',20);
commit;
insert into b_merge values(1,2,'zhangbin',30,'吉林');
insert into b_merge values(2,4,'yihe',33,'黑龍江');
insert into b_merge (id,aid,name,city) values(3,3,'fuguo','山東');
commit;
此時a_merge和b_merge表中資料如下:
SQL> select * from a_merge;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 20
2 zhangbin 21
3 fuguo 20
SQL> select * from b_merge;
ID AID NAME YEAR CITY
---------- ---------- ------------ ---------- ------------
1 2 zhangbin 30 吉林
2 4 yihe 33 黑龍江
3 3 fuguo 山東
然後再使用merge into用b_merge來更新a_merge中的資料:
merge into a_merge a using (select b.aid,b.name,b.year from b_merge b) c on (a.id=c.aid)
when matched then
update set a.year=c.year
when not matched then
insert(a.id,a.name,a.year) values(c.aid,c.name,c.year);
commit;
此時a_merge中的表資料截圖如下:
SQL> select * from a_merge;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 20
2 zhangbin 30
3 fuguo
4 yihe 33
2.只update模式
首先向b_merge中插入兩個資料，來為了體現出只update沒有insert，必須有一個資料是a中已經存在的
另一個資料時a中不存在的，插入資料語句如下:
insert into b_merge values(4,1,'liuwei',80,'江西');
insert into b_merge values(5,5,'tiantian',23,'河南');
commit;
此時a_merge和b_merge表資料截圖如下:
SQL> select * from a_merge;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 20
2 zhangbin 30
3 fuguo
4 yihe 33
SQL> select * from b_merge;
ID AID NAME YEAR CITY
---------- ---------- ------------ ---------- ------------
1 2 zhangbin 30 吉林
2 4 yihe 33 黑龍江
3 3 fuguo 山東
4 1 liuwei 80 江西
5 5 tiantian 23 河南
然後再次用b_merge來更新a_merge，但是僅僅update，沒有寫insert部分。
merge into a_merge a using (select b.aid,b.name,b.year from b_merge b) c on(a.id=c.aid)
when matched then
update set a.year=c.year;
commit;
merge完之後a_merge表資料如下:可以發現僅僅更新了aid=1的年齡，沒有插入aid=4的資料
SQL> select * from a_merge;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 80
2 zhangbin 30
3 fuguo
4 yihe 33
3.只insert模式
首先改變b_merge中的一個資料，因為上次測試update時新增的資料沒有插入到a_merge，這次可以用。
update b_merge set year=70 where aid=2;
commit;
此時a_merge和b_merge的表資料截圖如下:
SQL> select * from a_merge;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 80
2 zhangbin 30
3 fuguo
4 yihe 33
SQL> select * from b_merge;
ID AID NAME YEAR CITY
---------- ---------- ------------ ---------- ------------
1 2 zhangbin 70 吉林
2 4 yihe 33 黑龍江
3 3 fuguo 山東
4 1 liuwei 80 江西
5 5 tiantian 23 河南
然後用b_merge來更新a_merge中的資料，此時只寫了insert，沒有寫update:
merge into a_merge a using (select b.aid,b.name,b.year from b_merge b) c on(a.id=c.aid)
when not matched then
insert(a.id,a.name,a.year) values(c.aid,c.name,c.year);
commit;
此時a_merge的表資料截圖如下:
SQL> select * from a_merge;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 80
2 zhangbin 30
3 fuguo
4 yihe 33
5 tiantian 23
4.帶where條件的insert和update。
我們在on中進行完條件匹配之後，還可以在後面的insert和update中對on篩選出來的記錄再做一次條件判斷，用來控制哪些要更新，哪些要插入。
測試資料的sql程式碼如下，我們在b_merge修改了兩個人名，並且增加了兩個人員資訊，但是他們來自的省份不同，所以我們可以透過新增省份條件來控制哪些能修改，哪些能插入:
update b_merge set name='yihe++' where id=2;
update b_merge set name='liuwei++' where id=4;
insert into b_merge values(6,6,'ningqin',23,'江西');
insert into b_merge values(7,7,'bing',24,'吉安');
commit;
SQL> select * from a_merge;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 80
2 zhangbin 30
3 fuguo
4 yihe 33
5 tiantian 23
SQL> select * from b_merge;
ID AID NAME YEAR CITY
---------- ---------- ------------ ---------- ------------
1 2 zhangbin 70 吉林
2 4 yihe++ 33 黑龍江
3 3 fuguo 山東
4 1 liuwei++ 80 江西
5 5 tiantian 23 河南
6 6 ningqin 23 江西
7 7 bing 24 吉安
然後再用b_merge去更新a_merge，但是分別在insert和update後面新增了條件限制，控制資料的更新和插入:
merge into a_merge a using (select b.aid,b.name,b.year,b.city from b_merge b) c
on(a.id=c.aid)
when matched then
update set a.name=c.name where c.city != '江西'
when not matched then
insert(a.id,a.name,a.year) values(c.aid,c.name,c.year) where c.city='江西';
commit;
此時a_merge如下:
SQL> select * from a_merge;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 80
2 zhangbin 30
3 fuguo
4 yihe++ 33
5 tiantian 23
6 ningqin 23
已選擇6行。
5.無條件的insert。
有時我們需要將一張表中所有的資料插入到另外一張表，此時就可以新增常量過濾謂詞來實現，讓其只滿足匹配和不匹配，這樣就只有update或者只有insert。這裡我們要無條件全插入，則只需將on中條件設定為永假
即可。用b_merge來更新c_merge程式碼如下:
merge into c_merge c using (select b.aid,b.name,b.city from b_merge b) c on (1=0)
when not matched then
insert(c.id,c.name,c.city) values(b.aid,b.name,b.city);
commit;
a_merge、b_merge、c_merge表在merge之前的資料如下:
SQL> select * from a_merge;
ID NAME YEAR
---------- ------------ ----------
1 liuwei 80
2 zhangbin 30
3 fuguo
4 yihe++ 33
5 tiantian 23
6 ningqin 23
已選擇6行。
SQL> select * from b_merge;
ID AID NAME YEAR CITY
---------- ---------- ------------ ---------- ------------
1 2 zhangbin 70 吉林
2 4 yihe++ 33 黑龍江
3 3 fuguo 山東
4 1 liuwei++ 80 江西
5 5 tiantian 23 河南
6 6 ningqin 23 江西
7 7 bing 24 吉安
已選擇7行。
SQL> select * from c_merge;
ID NAME CITY
---------- ------------ ------------
2 zhangbin 吉林
4 yihe++ 黑龍江
3 fuguo 山東
1 liuwei++ 江西
5 tiantian 河南
6 ningqin 江西
7 bing 吉安
6.帶delete的update
merge提供了在執行資料操作時清除行的選項. 你能夠在when matched then update子句中包含delete子句.
delete子句必須有一個where條件來刪除匹配某些條件的行.匹配delete where條件但不匹配on條件的行不會被從表中刪除.
但我覺得這個跟帶where條件的update差不多，都是控制update，完全可以用帶where條件的update來實現
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2139948/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle merge into用法Oracle
- oracle merge into 用法詳解Oracle
- ORACLE merge用法詳解Oracle
- SQL中Merge的用法SQL
- oracle merge into用法(R1)Oracle
- oracle-merge用法詳解Oracle
- oracle-merge用法詳解 (轉)Oracle
- oracle中merge的用法，以及各版本的區別 CreateOracle
- ORACLE 中ROWNUM（偽列）用法解析（轉載）Oracle
- MySQL中merge表儲存引擎用法MySql儲存引擎
- SQLServer MERGE 用法SQLServer
- oracle 9i/10g merge 用法Oracle
- 【原】關於Oracle Merge操作的簡單用法Oracle
- 轉：oracle 9i/10g merge 用法Oracle
- Java 8 中 Map 騷操作之 merge() 的用法Java
- oracle中merge 語句使用Oracle
- sql server merge 的用法SQLServer
- MogDB/openGauss中merge的語法解析
- oracle中top用法Oracle
- Oracle中with的用法Oracle
- Oracle中group by用法Oracle
- nginx 配置解析（11）——mergeNginx
- 【 Oracle中rownum的用法 】Oracle
- Oracle 中 case的用法Oracle
- oracle_mergeOracle
- oracle中substr() instr() 用法Oracle
- oracle樹中prior的用法Oracle
- Oracle中rowid的用法Oracle
- 【oracle中rowid的用法】Oracle
- Oracle PL/SQL中EXCEPTION用法OracleSQLException
- oracle中copy from的用法Oracle
- ORACLE 中ROWNUM用法總結!Oracle
- ORACLE 中ROWNUM用法總結Oracle
- 深入解析Vue中watch的高階用法Vue
- sql中的group by 和 having 用法解析SQL
- Sql server 2005中output用法解析SQLServer
- oracle hint no_mergeOracle
- Oracle Merge語法Oracle