作用:
判断A表和C表是否满足某条件,如果满足则用C表去更新A表,如果不满足,则将C表数据插入A表。
用法:
MERGE INTO [target-table] A USING [source-table sql] C ON([conditional expression] and […]…)
WHEN MATCHED THEN
[UPDATE sql]
WHEN NOT MATCHED THEN
[INSERT sql]
DEMO:
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;
其中
select B.AID,B.NAME,B.YEAR from B_MERGE B) (AS) C
此处AS省略了。
作用:判断A表和C表是否满足ON后的条件,如果满足则用C表去更新A表,如果不满足,则将C表数据插入A表。
操作:
第一步:创建表
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;
第二步:插入数据
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 values(3,3,'fuguo',,'山东');
commit;
创建后得到表格如下:
第三步:使用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;
merge以后的表如下:
其中B表中AID2和3在A表中有,所以将A表更新了,2的YRAR变成了30,3的YEAR变成了空,B表中4在A表中没有所以插入数据。
————————————————
版权声明:本文为CSDN博主「玩人」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/jeryjeryjery/article/details/70047022