How to use a procedure to update a new table?

Question

I have a table called sales and which tracks all the columns such as Sale_Id, etc.. and a new table called sales_2 table which I'm looking to gather data for the past month only from the sales table. I would like to create a procedure that looks up what changed today at the sales table and go update the changed/added records into the sales_2 table. How can I write a query in which I can use the current_dt - 30 days structure to fetch the changed records?

Answer 1

you could use the dateadd function.

select dateadd(days ,-30,current_date()) before_30_days,  to_date('2013-05-08') as v1, dateadd(year, 2, to_date('2013-05-08')) as v;

The procedure could be something like this. -- untested need sample table structure

create or replace procedure TABLE_UPDATE()
returns varchar(200)
language sql
as
$$
declare
  v_sql varchar2(2000);
begin
  let declare varchar := 'update abc  set col1 = (select cc.col1 from cc where cc.sid1 = abc.sid1 and 
                            and dateadd(days ,-30,cc.date_modified));';
  execute immediate declare;

end;
$$;