--更新或插入 MERGE INTO tmp11 p USING tmp22 np ON (p.no1 = np.no1 and p.no2 = np.no2) WHEN MATCHED THEN UPDATE SET p.price = np.price WHEN NOT MATCHED THEN INSERT VALUES (np.no1, np.no2, np.price);
--比较 select * from ( select '原表'表名, tmp11.* from tmp11 where exists (select no1, no2 from tmp22 where tmp11.no1=tmp22.no1 and tmp11.no2=tmp22.no2) union select '新表'表名, tmp22.* from tmp22 ) order by no1, no2, 表名
select a.a,a.b from tab1 minus select b.a,b.b from tab2
然后匹配关键字,能匹配可以做更新
不能匹配的可以不做操作或者做插入操作
create table tmp11
(
no1 VARCHAR2(60),
no2 VARCHAR2(60),
price number(8)
); insert into tmp11 values ('A001', 'B001', 1);
insert into tmp11 values ('A002', 'B002', 2);
insert into tmp11 values ('A003', 'B003', 3);
insert into tmp11 values ('A004', 'B004', 4);
insert into tmp11 values ('A005', 'B005', 5);
commit;create table tmp22
(
no1 VARCHAR2(60),
no2 VARCHAR2(60),
price number(8)
); insert into tmp22 values ('A001', 'B001', 11);
insert into tmp22 values ('A002', 'B002', 22);
insert into tmp22 values ('A007', 'B009', 3);
commit;
delete tmp11;
delete tmp22;
select * from tmp11;
select * from tmp22;
--更新或插入
MERGE INTO tmp11 p
USING tmp22 np
ON (p.no1 = np.no1 and p.no2 = np.no2)
WHEN MATCHED THEN
UPDATE
SET p.price = np.price
WHEN NOT MATCHED THEN
INSERT
VALUES (np.no1, np.no2, np.price);
--比较
select * from
(
select '原表'表名, tmp11.* from tmp11
where exists (select no1, no2 from tmp22 where tmp11.no1=tmp22.no1 and tmp11.no2=tmp22.no2)
union
select '新表'表名, tmp22.* from tmp22
)
order by no1, no2, 表名
minus
select b.a,b.b from tab2