Home > PL/SQL > Oracle PL/SQL – Merge table

Oracle PL/SQL – Merge table

Thursday, 20 January, 2011 Leave a comment Go to comments

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;
Advertisements
Categories: PL/SQL Tags:
  1. Thursday, 20 January, 2011 at 5:05 pm

    Note: You cannot modify the merge “key”, that is not permitted.
    asktom

    merge_update_clause

  2. Thursday, 20 January, 2011 at 5:18 pm
    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))));
    
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: