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

please help.how accomplish following:

the table holds daily transaction data . aim update/insert values 3 of columns in current day's records using calculated values (of 3 columns) of yesterday's records. have last 40 days 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 code:

marge  (select trans_date, store, item, reason, col1, col2, col3  tb1 tb1.trans_date   = trunc(sysdate)) today  using   (select trans_date, store, item, reason, col1, col2, col3  tb1  tb1.trans_date = trunc(sysdate-1)) yesterday  when matched 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 insert (today.col1, today.col2, today.col3 ) values (          1, 1,           (select max(trans_date) tb1            tb1.trans_date < trunc(sysdate)-1)           , tb1.store=today.store           , tb1.item=today.item); 

---

please note: each day records may have duplicates 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 need create stored procedure. help

 create or replace procedure sp_test    // declare variables //  cursor c select trans_date, store, item, reason, col1, col2, col3    tb1 tb1.trans_date = trunc(sysdate-1);  begin      rec in c loop         // calculations //     select count(*) v_v1 tb1 tb1.trans_date = trunc(sysdate)     if v_v1=0       // insert     else         //do update     end if;     end loop;   exception          // exception part   end; 

if don't want duplicates use distinct in cursor query or use constraints in table

for history data use this

 create or replace procedure sp_test(p_date date)    // declare variables //  cursor c select trans_date, store, item, reason, col1, col2, col3    tb1 tb1.trans_date = trunc(p_date-1);  begin      rec in c loop         // calculations //     select count(*) v_v1 tb1 tb1.trans_date = trunc(p_date)     if v_v1=0       // insert     else         //do update     end if;     end loop;   exception          // exception part   end;