Re: Delete date

"ddf" <oratune_at_msn.com> schreef in bericht news:d90b62a6-74a5-4f2b-8cfa-2349b0f45383_at_v72g2000hsv.googlegroups.com... Comments embedded.

On Oct 23, 9:47 pm, muhammaddzulkarn..._at_gmail.com wrote:
> hello everyone,
>
> Can someone help me how to create sql statements to delete the table
> records and retain only 3 months of data. Concept should be like this:
>
> Delete all (TABLE_NAME) table records where the statistics_date month
> is < current month – 3.
>
> i tried this but its not working:
>
> delete from gen_api_performance_daily
> where statistics_date < add_months(sysdate,-3);
>

Define 'its not working'.because it certainly appears to do the job where I sit:

SQL> select count(*)
  2 from gen_api_performance_daily
  3 where statistics_date < add_months(sysdate, -3)   4 /

  COUNT(*)

      1000

SQL> delete from gen_api_performance_daily   2 where statistics_date < add_months(sysdate,-3);

1000 rows deleted.

SQL>
> and also how to create sql statements to delete the table records and
> retain only 1 year of data.
> And concept should be like this:
>
> Delete all (TABLE_NAME) table records where the statistics_year is =
> previous year – 1.
>

A similar statement would do the job:

SQL> insert into gen_api_performance_daily   2 select add_months(statistics_date, -24), statistics_year - 2   3 from gen_api_performance_daily;

3000 rows created.

SQL> commit;

Commit complete.

SQL> delete from gen_api_performance_daily   2 where statistics_year = to_number(to_char(add_months(sysdate, -24), 'RRRR'))
  3 /

3000 rows deleted.

SQL>
> I have no idea about it..

David Fitzjarrell

Shakespeare Received on Fri Oct 24 2008 - 08:43:32 CDT