CODEWITHSUNDEEP
sqloracle-database
Mike
Mike

Reputation: 55

To delete a row when sysdate is a certain value in oracle sql

I am having a problem deleting a row in my table after it has existed for a certain amount of time. To be more specific, I have a column named 'hiredate' and I want to delete a row when the hiredate of that row is older than 5 years.

Upvotes: 1

Views: 2180

Answers (1)

David Faber
David Faber

Reputation: 12485

With respect to the code you have in your comment:

delete from emp where p_id = (select p_id from emp where sysdate = hiredate+1825);

It's totally unnecessary to use a subquery here. You could simply do:

DELETE FROM emp WHERE sysdate = hiredate + 1825;

Of course, that will only work where the time portion of hiredate is exactly equal to the time portion of SYSDATE! Also, 1825 is not five years; it is five times 365. A better way of doing this in Oracle would be:

DELETE FROM emp
 WHERE hiredate < TRUNC(ADD_MONTHS(SYSDATE, -60));

or, if you're certain you want to delete only one day's results:

DELETE FROM emp
 WHERE hiredate >= TRUNC(ADD_MONTHS(SYSDATE, -60)) - 1
   AND hiredate < TRUNC(ADD_MONTHS(SYSDATE, -60));

However, if you're running this as a one-time query or even as a scheduled job, you'll want to delete everything over five years of age; a scheduled job, for example, could always fail one night and succeed the next, leaving you with an odd day's records in EMP.

By the way, ADD_MONTHS() is the safest way of adding or subtracting years in Oracle; using year to month intervals can fail during leap years. For example, the following would fail on February 29, 2016:

DELETE FROM emp
 WHERE hiredate < TRUNC(SYSDATE) - INTERVAL '5' YEAR;

The right side of the equation would calculate to a date of February 29, 2011, which doesn't exist.

Upvotes: 4

Related Questions