Re: Delete date
Date: Fri, 24 Oct 2008 06:07:06 -0700 (PDT)
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 /
SQL> delete from gen_api_performance_daily 2 where statistics_date < add_months(sysdate,-3);
1000 rows deleted.
> 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> delete from gen_api_performance_daily
2 where statistics_year = to_number(to_char(add_months(sysdate,
3000 rows deleted.
> I have no idea about it..
David Fitzjarrell Received on Fri Oct 24 2008 - 08:07:06 CDT