Home » SQL & PL/SQL » SQL & PL/SQL » Query help (Oracle 10.2.0.4, AIX 5.3)
Query help [message #582305] Tue, 16 April 2013 14:57 Go to next message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
Hi,

I've a below table which contains old_cust_id and new_cust_id. Now i need to update all old_cust_id's to new_cust_id in another table. I've to keep only one record for each cust id for each prod_cd. Can someone help on writing query.

Table which contains old and new cust id's

SQL> create table tst_relink (old_cust_id number(10), new_cust_id number(10));
 
Table created

SQL> insert into tst_relink (old_cust_id, new_cust_id) values (123, 345);
 
1 row inserted
 
SQL> insert into tst_relink (old_cust_id, new_cust_id) values (234, 345);
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from tst_relink;
 
OLD_CUST_ID NEW_CUST_ID
----------- -----------
        123         345
        234         345



Table which needs to be updated based on above tst_relink table


create table tst_email_data (cust_id number(10),
                             prod_cd varchar2(10),
                             email_addr varchar2(100),
                             email_scr  number(10),
                             last_rec_dt date
                             );

alter table tst_email_data
  add constraint PK_EMAIL_TST primary key (CUST_ID, PROD_CD)
  using index 
  ;

Insert into tst_email_data values (123, 'P1', 'tstp1@gmail.com', 100, to_date('20101112','YYYYMMDD'));
Insert into tst_email_data values (234, 'P1', 'tstp1@gmail.com', 100, to_date('20110512','YYYYMMDD'));
Insert into tst_email_data values (345, 'P1', 'tstp1@yahoo.com', 50, to_date('20110612','YYYYMMDD'));

Insert into tst_email_data values (123, 'P2', 'tstp2@yahoo.com', 80, to_date('20120404','YYYYMMDD'));
Insert into tst_email_data values (234, 'P2', 'tstp2@gmail.com', 100, to_date('20120504','YYYYMMDD'));
Insert into tst_email_data values (345, 'P2', 'tstp2@gmail.com', 100, to_date('20120704','YYYYMMDD'));

commit;

SQL> select * from tst_email_data;
 
    CUST_ID PROD_CD    EMAIL_ADDR                                                                         EMAIL_SCR LAST_REC_DT
----------- ---------- -------------------------------------------------------------------------------- ----------- -----------
        123 P1         tstp1@gmail.com                                                                          100 11/12/2010
        234 P1         tstp1@gmail.com                                                                          100 5/12/2011
        345 P1         tstp1@yahoo.com                                                                           50 6/12/2011
        123 P2         tstp2@yahoo.com                                                                           80 4/4/2012
        234 P2         tstp2@gmail.com                                                                          100 5/4/2012
        345 P2         tstp2@gmail.com                                                                          100 7/4/2012
 
6 rows selected



Now i want to update old_cust_id to new_cust_id and need to keep most recent data based on email_score and last_rec_dt for each prod_cd. (i.e. for Prod_cd P1 i need to update cust_id to 345 but keep the email_addr tstp1@gmail.com as this has email_score of 100).

My Output will be:


    CUST_ID PROD_CD    EMAIL_ADDR             EMAIL_SCR   LAST_REC_DT
----------- ---------- ---------------------- ----------- -----------
        345 P1         tstp1@gmail.com           100        5/12/2011
        345 P2         tstp2@gmail.com           100        7/4/2012



Will it be possible to do it in one update statement. Appreciate your help.

Thanks & Regards
Sri
Re: Query help [message #582313 is a reply to message #582305] Wed, 17 April 2013 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Will it be possible to do it in one update statement.


If you have to delete and update you cannot do it in a single UPADTE, this is obvious.

Quote:
need to keep most recent data based on email_score and last_rec_dt for each prod_cd.


This sentence is ambiguous.
What do you want? The most recent data (so based on date) and if several data has same date the greatest score?
Or the greatest score and if there are several with the same one the greatest date?
Or the least score? Or...?

Regards
Michel
Re: Query help [message #582369 is a reply to message #582313] Wed, 17 April 2013 09:40 Go to previous messageGo to next message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
Hi Michael,

Yes, i agree, cannot do DELETE and UPDATE in one statement. Can it be done 2 statements one UPDATE and One DELETE or do i need to write pl/sql block?

I want to keep most recent data based on Greatest score and Greatest date for each prod_cd (Order by Prod_cd, Email_score desc, last_rec_dt desc)

1) If records with same score and different dates (1/1/2011 and 2/1/2012) then keep the record with greatest date (i.e. 2/1/2012)
2) If records with different score (80 and 100) and same dates then keep the record with greatest score (i.e. record with score of 100)
3) If records with different score (80 and 100) and different dates (score 80 on date 2/1/2012 and score 100 on date 1/1/2011) then keep the record with greatest score (i.e. record with score 100 and date 1/1/2011).

Thanks
Sri
Re: Query help [message #582370 is a reply to message #582369] Wed, 17 April 2013 10:33 Go to previous messageGo to next message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
Hi Michael,

I've written below pl/sql block to do the update and delete. But not sure about performance if i've million records in tst_email_data table. Any suggestions?


DECLARE
  v_rec_num     NUMBER(10) := 0;

  v_old_indiv_id     INTEGER;
  v_new_indiv_id     INTEGER;
  v_new_indiv_id_sav INTEGER := 0;

  v_email_addr VARCHAR2(100);
  v_email_qlty_scr NUMBER(10) := 0;

  v_indiv_id NUMBER(10);

  v_prod_cd VARCHAR2(10 BYTE);

  --for secure table
  v_last_rec_dt_sav    DATE;
  v_secure_rec_num     NUMBER(10) := 0;
  v_email_addr_sav     VARCHAR2(100) := 'X';
  v_prod_cd_sav        VARCHAR2(6) := 'X';
  v_prog_cd_sav        VARCHAR2(8) := 'X';
  v_email_qlty_scr_sav NUMBER(10) := 0;
  v_null_update_rowid  VARCHAR2(20 BYTE) := NULL;
  v_null_update_flg    NUMBER(1, 0) := 0;
  
  ct_del_email_prfl INTEGER := 0;
  ct_upd_email_prfl INTEGER := 0;

  CURSOR relink_secure_cur IS
    SELECT distinct new_cust_id as cust_id
      FROM tst_relink
     WHERE old_cust_id != new_cust_id
     order by new_cust_id;

  CURSOR email_cur IS
    select distinct a.rowid,
                    a.cust_id,
                    a.prod_cd,
                    a.email_addr,
                    a.LAST_REC_DT,
                    a.EMAIL_SCR
      from tst_email_data a, tst_RELINK d
     where d.new_cust_id = v_new_indiv_id
       and (a.cust_id = v_new_indiv_id or a.cust_id = d.old_cust_id)
     order by a.prod_cd,
              a.EMAIL_SCR desc,
              a.LAST_REC_DT desc,
              a.cust_id desc;

begin

  FOR main_cur IN relink_secure_cur LOOP
    BEGIN
      v_secure_rec_num    := v_secure_rec_num + 1;
      v_new_indiv_id      := main_cur.cust_id;
      v_prod_cd_sav       := 'X';
      v_null_update_rowid := NULL;
      v_null_update_flg   := 0;
      
      FOR cur in email_cur LOOP
        begin
          v_prod_cd    := cur.prod_cd;
          v_email_addr := cur.email_addr;
          v_indiv_id   := cur.cust_id;
          
          dbms_output.put_line('Processing cust_id --> '||v_indiv_id);
        
          if v_prod_cd != v_prod_cd_sav then
            DBMS_OUTPUT.PUT_LINE('>>>>>email_PRFL_tst null last update ' || v_indiv_id || ' to ' || v_new_indiv_id);
         
            v_prod_cd_sav        := v_prod_cd;
            v_last_rec_dt_sav    := cur.last_rec_dt;
            v_email_addr_sav     := v_email_addr;
            v_email_qlty_scr_sav := cur.email_scr;
          end if;
          begin
            update tst_email_data
               set email_addr     = v_email_addr_sav,
                   cust_id       = v_new_indiv_id,
                   email_scr      = v_email_qlty_scr_sav,
                   last_rec_dt   = greatest(v_last_rec_dt_sav,cur.last_rec_dt)
             where rowid = cur.rowid;
            ct_upd_email_prfl   := ct_upd_email_prfl + 1;
            v_null_update_rowid := cur.rowid;
            v_null_update_flg   := 0;
            DBMS_OUTPUT.PUT_LINE('>>>INFO: EMAIL_PRFL -1 update ' ||
                                 cur.cust_id || ' to ' || v_new_indiv_id ||
                                 ' email to ' || v_email_addr_sav || ' ' ||
                                 to_char(v_last_rec_dt_sav, 'yyyymmdd'));
          exception
            WHEN DUP_VAL_ON_INDEX THEN
              -- CNSLDTN_INSERT_TB('EMAIL_PRFL', cur.rowid);         
              DELETE FROM tst_email_data WHERE rowid = cur.rowid;
              ct_del_email_prfl := ct_del_email_prfl + 1;
              v_null_update_flg := 1;
              DBMS_OUTPUT.PUT_LINE('>>>INFO: EMAIL_PRFL -2 delete old_id ' ||
                                   v_indiv_id || ' email is ' ||
                                   v_email_addr || ' ' ||
                                   to_char(v_last_rec_dt_sav, 'yyyymmdd'));
          end;
        end;
      END LOOP;
      
     end;
    END LOOP;
  
    DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('>' || LPAD(TO_CHAR(v_secure_rec_num), 12, ' ') || ' To Be Consolidated');
    DBMS_OUTPUT.PUT_LINE('>' || LPAD(TO_CHAR(ct_upd_email_prfl), 12, ' ') ||' email_prfl          records updated by consolidation');
    DBMS_OUTPUT.PUT_LINE('>' || LPAD(TO_CHAR(ct_del_email_prfl), 12, ' ') ||' email_prfl          records Consolidated');
    DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------');
end;





Thanks
Sri
Re: Query help [message #582371 is a reply to message #582370] Wed, 17 April 2013 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Given what you want to update (2 cust_id with same new cust_id) you must disable the primary key first and re-enable it at the end:
SQL> select * from tst_email_data ;
   CUST_ID PROD_CD    EMAIL_ADDR       EMAIL_SCR LAST_REC_DT
---------- ---------- --------------- ---------- -------------------
       123 P1         tstp1@gmail.com        100 12/11/2010 00:00:00
       234 P1         tstp1@gmail.com        100 12/05/2011 00:00:00
       345 P1         tstp1@yahoo.com         50 12/06/2011 00:00:00
       123 P2         tstp2@yahoo.com         80 04/04/2012 00:00:00
       234 P2         tstp2@gmail.com        100 04/05/2012 00:00:00
       345 P2         tstp2@gmail.com        100 04/07/2012 00:00:00

6 rows selected.

SQL> select * from tst_relink;
OLD_CUST_ID NEW_CUST_ID
----------- -----------
        123         345
        234         345

2 rows selected.

SQL> alter table tst_email_data disable constraint PK_EMAIL_TST;

Table altered.

SQL> update tst_email_data m
  2  set cust_id = (select new_cust_id from tst_relink r where r.old_cust_id = m.cust_id)
  3  where exists (select null from tst_relink r where r.old_cust_id = m.cust_id)
  4  /

4 rows updated.

SQL> delete tst_email_data 
  2  where rowid in 
  3        ( select rid 
  4          from ( select rowid rid,
  5                        rank() over 
  6                          (partition by cust_id, prod_cd 
  7                           order by email_scr desc, last_rec_dt desc)
  8                          rk
  9                 from tst_email_data )
 10          where rk != 1 )
 11  /

4 rows deleted.

SQL> alter table tst_email_data enable constraint PK_EMAIL_TST;

Table altered.

SQL> select * from tst_email_data ;
   CUST_ID PROD_CD    EMAIL_ADDR       EMAIL_SCR LAST_REC_DT
---------- ---------- --------------- ---------- -------------------
       345 P1         tstp1@gmail.com        100 12/05/2011 00:00:00
       345 P2         tstp2@gmail.com        100 04/07/2012 00:00:00

2 rows selected.

Regards
Michel
Re: Query help [message #582380 is a reply to message #582371] Wed, 17 April 2013 15:12 Go to previous message
sspn2010
Messages: 145
Registered: October 2008
Senior Member
Thank you Michael. It's working.

Thanks
Sri
Previous Topic: print a message
Next Topic: Problem with Sys_refcursor
Goto Forum:
  


Current Time: Sun Aug 31 03:50:19 CDT 2014

Total time taken to generate the page: 0.09253 seconds