CODEWITHSUNDEEP
sqloracle-database
vkreddy
vkreddy

Reputation: 191

How to delete records after archiving?

Am archiving last 4 to 12 months of data. Below query does the job.

insert into process_state_archive
  select *
    from process_state
   where tstamp BETWEEN ADD_MONTHS(trunc(SYSDATE, 'MONTH'), -12) AND
         LAST_DAY(TRUNC(SYSDATE, 'MONTH') - 4);

Now I want to delete 4 to 12 months data in process_state table. Where to add this condition in the above query? Can anyone please tell me?

Upvotes: 0

Views: 89

Answers (1)

San
San

Reputation: 4538

Here it goes (not tested)

DECLARE
  v_insert_count number;
  v_delete_count number;
BEGIN
  insert into process_state_archive 
  select * 
    from process_state 
    where tstamp BETWEEN ADD_MONTHS(trunc(SYSDATE,'MONTH'), -12) AND LAST_DAY(TRUNC(SYSDATE,  'MONTH') - 4);

  v_insert_count := sql%rowcount;

  delete process_state 
   where  tstamp BETWEEN ADD_MONTHS(trunc(SYSDATE,'MONTH'), -12) AND LAST_DAY(TRUNC(SYSDATE,  'MONTH') - 4);

  v_delete_count := sql%rowcount;

  if v_insert_count = v_delete_count then
    commit;
  else
    rollback;
  end if;

END;
/

sql%rowcount returns count of rows effected by the last sql statement.

Upvotes: 2

Related Questions