Home
>
PL/SQL > Oracle PL/SQL – Merge table
Oracle PL/SQL – Merge table
Thursday, 20 January, 2011
Baur
clear;
create table myTable
(pid number, sales number, status varchar2(6));
create table myTable2
(pid number, sales number, status varchar2(6));
insert into myTable values(2,24,'CURR');
insert into myTable values(3, 0,'OBS' );
insert into myTable values(4,42,'CURR');
insert into myTable values(6,56,'C44URR');
insert into myTable2 values(1,12,'CURR');
insert into myTable2 values(2,13,'NEW' );
insert into myTable2 values(3,15,'CURR');
select * from myTable;
select 't2' from dual;
merge into myTable2 m
using myTable d
on (m.pid = d.pid)
when matched then
update
set m.sales = d.sales, m.status = d.status
when not matched then
insert values (d.pid, d.sales, d.status);
delete myTable2 where pid in (select m.pid from myTable2 m where m.pid not in (select pid from myTable));
select * from myTable order by pid;
select * from myTable2 order by pid;
drop table myTable;
drop table myTable2;
Like this:
Be the first to like this post.
Note: You cannot modify the merge “key”, that is not permitted.
asktom
merge_update_clause
CLEAR; merge into tempReport01 asms using (SELECT srv1.ID, srv1.BACKID, srv1.TYPE, srv1.NAME, cms.address, cms.city_code FROM tbl_common@srv1 srv1 JOIN tRefTable cms ON cms.code = srv1.BACKID WHERE srv1.CLASS = 1) srv1 on (srv1.ID = asms.srv1ID) when matched then update set asms.BACKID = srv1.BACKID, asms.CITY_CODE = srv1.CITY_CODE, asms.TYPE = srv1.TYPE, asms.NAME = srv1.NAME, asms.LOCATION = srv1.ADDRESS when not matched then insert (asms.MODEL_ID, asms.VENDOR_ID, asms.term_id,asms.srv1ID,asms.BACKID,asms.CITY_CODE,asms.TYPE,asms.NAME,asms.LOCATION) VALUES (0,0,srv1.ID,srv1.ID, srv1.BACKID, srv1.CITY_CODE, srv1.TYPE, srv1.NAME, srv1.ADDRESS); DELETE tempReport01 WHERE srv1ID IN (SELECT m.srv1ID FROM tempReport01 m WHERE m.srv1ID NOT IN (SELECT ID FROM ((SELECT srv1.ID, srv1.BACKID, srv1.TYPE, srv1.NAME, cms.address, cms.city_code FROM tbl_common@srv1 srv1 JOIN tRefTable cms ON cms.code = srv1.BACKID WHERE srv1.CLASS = 1))));ppp