1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to delete data with condition?

Discussion in 'SQL PL/SQL' started by 24042014a, Jun 18, 2014.

  1. 24042014a

    24042014a Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    i have the foloowing table:

    Mytable

    Code (Text):
    Id name    Address   date                                          Company
    10 John    Florida        2014-05-02 00:00:00,123456                   IBM
    20 Smith   London       2014-05-02 01:00:00,123456               IBM
    30 Jenny   Brighton      2014-05-02 01:10:00,123456              IBM
    40 Benny  VERSAILLES  2014-05-02 01:15:00,123456              IBM
    what i'm trying to do is to delete the last recent record from 15 previous minutes

    So first i check if this is working:
    Code (Text):
    SELECT ID
    ,NAME
    ,ADDRESS
    ,DATE
    FROM MyTbale
    WHERE COMPANY= 'IBM'
    AND DATE BETWEEN systimestamp - INTERVAL '15' MINUTE
    AND SYSTIMESTAMP
    It's working fine and now i know datas from 15 mns are:
    30 Jenny Brighton 2014-05-02 01:10:00,123456 IBM
    40 Benny VERSAILLES 2014-05-02 01:15:00,123456 IBM

    Question:
    How can delete these records which are the last from previous 15mn

    I try this but it's not working:
    Code (Text):
    DELETE WHERE ID = (
    FROM MyTbale
    WHERE COMPANY= 'IBM'
    AND DATE BETWEEN systimestamp - INTERVAL '15' MINUTE
    AND SYSTIMESTAMP
    Any help please, thank you
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You're making this harder than it needs to be:


    Code (SQL):

    DELETE FROM mytable
    WHERE id IN
    (SELECT ID
    ,NAME
    ,ADDRESS
    ,DATE
    FROM MyTbale
    WHERE COMPANY= 'IBM'
    AND DATE BETWEEN systimestamp - INTERVAL '15' MINUTE
    AND SYSTIMESTAMP);
     
     
  3. 24042014a

    24042014a Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    i have this error with your code:
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Copy and paste error but you could have deleted the other column names from the select list:


    Code (SQL):

    DELETE FROM mytable
    WHERE id IN
    (SELECT ID
    FROM MyTable
    WHERE COMPANY= 'IBM'
    AND DATE BETWEEN systimestamp - INTERVAL '15' MINUTE
    AND SYSTIMESTAMP);
     
     
  5. 24042014a

    24042014a Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    This one is working fine

     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    And you have learned something.
     
  7. 24042014a

    24042014a Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    yes i learned a lot, thank you