This discussion is archived
3 Replies Latest reply: Jan 3, 2013 6:21 AM by 947163 RSS

Delete rows older than given number of hours

947163 Newbie
Currently Being Moderated
Hi,

I have a table student which contains the following columns

ID Number(5)
Name varchar2(20)
Strt_Time timestamp(6)

Strt_time is the timestamp when the particular row is inserted in the table.

I need a query to delete all the rows inserted before given particular number of hours.

For Example. If i pass the input paramter as 2 all the rows inserted before 2 hours from now should get deleted.
  • 1. Re: Delete rows older than given number of hours
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Here's one way:
    DELETE  student
    WHERE     strt_time < SYSTIMESTAMP - INTERVAL '2' HOUR
    ;
    Or, if you're getting a NUMBER parameter like :num_hours, then
    DELETE  student
    WHERE     strt_time < SYSTIMESTAMP - NUMTODSINTERVAL (:num_hours, 'HOUR')
    ;
     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and the results you want from that data.
    In the case of a DML operation (such as DELETE) the sample data should show what the tables are like before the DML, and the results will be the contents of the changed table after the DML.
    Explain, using specific examples, how you get those results from that data.
    If your results depend on the current time and/or parammeters, then give a couple of different examples using the same sample data. For example "If I run this at 11:00 on January 3, 2013, with :num_hours=12, then the table should be left with ... but if I run it at 13:00 on January 3, 2013, with :num_hours= 24, then I should get ..."
    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
  • 2. Re: Delete rows older than given number of hours
    947163 Newbie
    Currently Being Moderated
    Hi,

    DELETE FROM STU
    WHERE     STRT_TIME < SYSTIMESTAMP - INTERVAL '2' HOUR

    The above query is working perfectly fine.

    Please can u be more elaborative on the below mentioned query.

    I need to pass number of hours as parameter for the query and i have difficulty in executing the below mentioned query

    DELETE FROM STU
    WHERE     STRT_TIME < SYSTIMESTAMP - NUMTODSINTERVAL (:num_hours, 'HOUR');
  • 3. Re: Delete rows older than given number of hours
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    944160 wrote:
    ... I need to pass number of hours as parameter for the query and i have difficulty in executing the below mentioned query
    What front end are you using? How are you getting and passing the parameter? The example I gave
    DELETE FROM STU
    WHERE     STRT_TIME < SYSTIMESTAMP - NUMTODSINTERVAL (:num_hours, 'HOUR');
    works in SQL*Plus (among others), assuming you have previously defined and set the bind variable :num_hours in the same session. For example:
    VARIABLE  num_hours    NUMBER;
    EXEC     :num_hours := 2;
    
    
    DELETE  FROM STU
    WHERE     STRT_TIME < SYSTIMESTAMP - NUMTODSINTERVAL (:num_hours, 'HOUR');
    Instead of a bind variable like :num_hours, you can use any expression that evaluates to a NUMBER, including
    <ul>
    <li> a SQL*Plus substitution variable
    <li> a scalar sub-query involving any table(s)
    <li> a PL/SQL pacakge variable
    <li> a function call
    <li> some combination of the above, using CASE and/or arithmetic operators, such as + and -.
    </ul>

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points