是这样的,我有表A,现在有个select 出来的集合B,还有表C,(因为insert目标表是2个,所以不能用merge into ps:或者哪个大牛有好办法。。求教。)我需要用表A的数据跟集合B数据, 根据字段a,b做判断(where A.a=B.a and A.b=B.b),如果表A有这条记录就insert 到表C,如果没有这个记录,就Insert到表A, 求教各位大牛 Insert all怎么写。。刚接触oracle 2天。希望大家能教教我。
调试欢乐多
然后进行C表的插入:insert into C select * from A,B where A.a=B.a and A.b=B.b
insert into C表
select b表字段
from B表
where exists (select A表.字段a
from A表
where A表.a=B表.a and A表.b=B表.b)
插入A表
insert into A表
select b表字段
from B表
where not exists (select A表.字段a
from A表
where A表.a=B表.a and A表.b=B表.b)
清单如下:
create table a(a varchar2(10),b varchar2(10),c varchar2(10));
create table b(a varchar2(10),b varchar2(10),c varchar2(10));
create table c(a varchar2(10),b varchar2(10),c varchar2(10));
insert into a(a,b,c) values(1,2,1);
insert into a(a,b,c) values(1,3,1);
insert into b(a,b,c) values(1,2,2);
insert into b(a,b,c) values(1,4,2);
commit;
select * from a;
select * from b;
select * from c;
insert all
when c4 is not null then
into c (a,b,c) values(c1,c2,c3)
else
into a (a,b,c) values(c1,c2,c3)
select b.a as c1,b.b as c2,b.c as c3,a.a as c4
from B left join A on A.a=B.a and A.b=B.b;
select * from a;
select * from c;