updating select query-1 using select query-2 in the same table

Question

Please help.How do I accomplish the following:

the table holds daily transaction data . The aim is to update/insert values into 3 of the columns in the current day's records using the calculated values (of those 3 columns) of yesterday's records. I have last 40 days to update based on:

    trunc(sysdate)-39 = calculated value of trunc(sysdate)-40
    trunc(sysdate)-38 = calculated value of trunc(sysdate)-39
    trunc(sysdate)-37 = calculated value of trunc(sysdate)-36
    .
    .
    .
    .
    trunc(sysdate)= calculated value of trunc(sysdate)-1.

example of my code:

marge into

(select trans_date, store, item, reason, col1, col2, col3 
from tb1 where tb1.trans_date   = trunc(sysdate)) today

using 

(select trans_date, store, item, reason, col1, col2, col3 
from tb1 
where tb1.trans_date = trunc(sysdate-1)) yesterday

when matched then
update set
(today.col1 = yesterday.col1 + 1
today.col2 = decode(yesterday.reason,today.reason,today.col2+1,1)
today.col3 = yesterday.trans_date)

WHEN NOT MATCHED THEN
INSERT (today.col1, today.col2, today.col3 )
VALUES (
         1, 1, 
         (select max(trans_date) from tb1 
          where tb1.trans_date < trunc(sysdate)-1)
          and tb1.store=today.store
          and tb1.item=today.item);

please note: each day records may have duplicates as following.

today:

    trans_date  store  item   reason         col1  col2  col3    ***(expected values)***

    14/04/14    999   100  'short supply'     -     -     -   ==> 2,2,13/04/14
    14/04/14    999   100  'short supply'     -     -     -   ==> 2,2,13/04/14
    14/04/14    998   101  'Damaged'          -     -     -   ==> 2,2,11/04/14
    14/04/14    990   105  'Returned'         -     -     -   ==> 2,1,13/04/14
    14/04/14    995   107  'Returned'         -     -     -   ==> 1,1,14/04/14

yesterday:

    trans_date  store  item   reason         col1  col2  col3

    13/04/14    999   100  'short supply'    1  1   13/04/14  
    13/04/14    999   100  'short supply'    1  1   13/04/14 
    13/04/14    998   101  'Damaged'         1  1   11/04/14
    13/04/14    990   105  'Transferred'     1  1   13/04/14

if it helps: there will be about 450000 records per day to update/insert. updating day to day records can be done through a stored procedure. But the challenge is to update the history with one go. — user3531676, Apr 14 '14 at 14:18

Rock 'em · Answer 1 · 2014-04-15T05:30:07.470

If you need it often create a Stored procedure. That would help

 create or replace procedure SP_TEST
 as 
 // declare your variables //
 cursor c is select trans_date, store, item, reason, col1, col2, col3 
  from tb1 where tb1.trans_date = trunc(sysdate-1);
 begin

    for rec in c loop
        // do your calculations //
    select count(*) into v_v1 from tb1 where tb1.trans_date = trunc(sysdate)
    if v_v1=0 then
      // do insert
    else 
       //do update
    end if;
    end loop;
  exception
         // exception part
  end;

if you don't want duplicates use DISTINCT in your cursor query or use constraints in the table

For history data use something like this

 create or replace procedure SP_TEST(P_DATE DATE)
 as 
 // declare your variables //
 cursor c is select trans_date, store, item, reason, col1, col2, col3 
  from tb1 where tb1.trans_date = trunc(P_DATE-1);
 begin

    for rec in c loop
        // do your calculations //
    select count(*) into v_v1 from tb1 where tb1.trans_date = trunc(P_DATE)
    if v_v1=0 then
      // do insert
    else 
       //do update
    end if;
    end loop;
  exception
         // exception part
  end;

Thanks Rock'em. I will give it a try. Was just wondering how the history data can be updated though... — user3531676, Apr 14 '14 at 22:42
Pass parameter to the procedure say P_DATE which will be the date you process. and in cursor query where condition use P_DATE-1. so you take the data from the previous day of P_DATE,process it and store for P_DATE. so you can give any date back and process the history data. — Rock 'em, Apr 15 '14 at 05:26
I have edited the answer. check it ,if its working accept the answer by clicking the tick symbol. — Rock 'em, Apr 15 '14 at 05:32

updating select query-1 using select query-2 in the same table

1 Answers1