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!

Simple query to delete rows from table

Discussion in 'SQL PL/SQL' started by simona007, Jan 7, 2009.

  1. simona007

    simona007 Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    80
    hi friends,

    I have the following data set in a table DIGITAL_REF(ID, REF_NUM, FLAG).

    Code (Text):

    ID REF_NUM FLAG
    L000304T 117930 M
    L001538N 126199 R
    L001538N 126191 M
    L006339H 150427 N
    L008519X 139085 R
    L008519X 139075 M
    L008579J 402452 R
    L008579J 144668 M
    L008579J 402429 R
    L043884W 447519 R
    L043885H 303383 C
    L043886X 303383 R
     
    Now for my purpose, the valid records are only those with flag = R

    I want to delete only those records with the IDs are in valid record but REF_NUM is not corresponding to that valid id.

    For e.g take this case
    L001538N 126199 R --- is valid hence
    L001538N 126191 M -- is invalid, and is to be deleted

    similarly the other invalid records to be deleted are
    L008519X 139075 M
    L008579J 144668 M

    I tried the query below to select, but it is not working
    Code (Text):
    select *
    from DIGITAL_REF del
    Where del.REF_NUM not in
    (select sel.REF_NUM -- selects all records with FLAG as R
    from FLAG_LKP sel
    where sel. FLAG = 'R'
    and sel.ID = del.ID)
     
    The catch is that in the outer query you should not use del.FLAG<> R.
    I have put FLAG = R so as to present the problem simply. Actually the selection criteria (which will replace FLAG = R) results from a multiple table join. To put FLAG <> R in outer query would be to replicate the multi table join which I don't want.

    I know this is actually quite simple for you gurus but i can't seem to be able to do it, could you provide a little help please.

    Thanks :)
    Simona
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Hi simona,

    Following query may work (Not tested). The queries are in assumption that before deletion you need ensure that IDs have the flag 'R' in both the tables . But you never mentioned about table flag_lkp !!!

    1. Self join

    Code (Text):

    DELETE FROM digital_ref
          WHERE (ID, ref_num, flag) IN (
                   SELECT del2.*
                     FROM digital_ref del1, digital_ref del2
                    WHERE del1.flag = 'R'
                      AND del2.flag <> del1.flag
                      AND del1.ID = del2.ID
                      AND del2.ref_num <> del1.ref_num
                      AND (del1.ID, del1.ref_num) IN (SELECT ID, ref_num
                                                        FROM flag_lkp
                                                       WHERE flag = 'R'))
     

    2. Using Correlated Sub query


    Code (Text):

    DELETE FROM digital_ref del2
          WHERE EXISTS (
                   SELECT NULL
                     FROM digital_ref del1
                    WHERE del1.flag = 'R'
                      AND del2.flag <> del1.flag
                      AND del1.ID = del2.ID
                      AND del2.ref_num <> del1.ref_num
                      AND EXISTS (
                             SELECT NULL
                               FROM flag_lkp sel
                              WHERE flag = 'R'
                                AND sel.ID = del1.ID
                                AND sel.ref_num = del1.ref_num))
     
    change the query according to your need . All the Best :)
     
  3. prithviraaj

    prithviraaj Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi Simona
    I think this query might work. Please check. Inside query return the data with flag other than R.
    and outer query will delete these rows.
    Code (Text):

    delete from DIGITAL_REF del
    Where del.REF_NUM in
        (
          select sel.REF_NUM from FLAG_LKP sel
          where sel. FLAG in( 'M','N','C')
        )
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Requirement seems to be different from what you tried .