wants to delete rows older than 2 rows in a colomn

Database
Enthusiast

wants to delete rows older than 2 rows in a colomn

i am running job today 2007-02-06 and i want to delete data prior to 2005-02-06
please help me in this regard

Thanks in advance
chinna
4 REPLIES
Enthusiast

Re: wants to delete rows older than 2 rows in a colomn

First try to build an SQL select query with a 'where clause' of the date u mentioned.
Check if the data u intend to delete is selected or not.
Then change the select to delete with same where clause.
Test it on few rows first.
If the result is as expected then change the where condition accordingly and then run it on production data.
Hope this helps.

Enthusiast

Re: wants to delete rows older than 2 rows in a colomn

Hello,
You have the rough way of using the following

SELECT DATE, DATE - 730;

this will give you the exact date 2 years ago since each year is considered to have 365 days.

The other more perfect way would be to cross examine against your system TIME tables and get the date based on year_id and date_of_day. But this is more environment specific and how the TIME dimension has been set in your environment.

You can use something like below:
DELETE FROM tab_a
WHERE col_a < DATE - 730;

I would take the previous advice of first running this as a SELECT as follows:
SELECT * FROM tab_a
WHERE col_a < DATE - 730;

This will ensure that you are seeing the exact data that you want to delete and this is used only as a precautionary measure. Hope that helps.
Enthusiast

Re: wants to delete rows older than 2 rows in a colomn

I would take the previous advice of first running this as a SELECT as follows:
SELECT * FROM tab_a
WHERE col_a < DATE - 730;

perhaps we should take year of 366 days into consideration

and first select ,then delete is a very good method ,

we may rollback when errors happend
Enthusiast

Re: wants to delete rows older than 2 rows in a colomn

WHERE datecol < ADD_MONTHS(CURRENT_DATE, -24);