7 Replies Latest reply on Aug 12, 2008 6:53 PM by SomeoneElse

    Delete with IN and EXISTS

    652108
      Hi,
      I was suggested by one of the oracle forums member
      that

      DELETE FROM PYMT_DTL WHERE CLM_CASE_NO IN
      (SELECT CLM_CASE_NO FROM TEMP_ARCHIVE1 );

      is same as

      DELETE FROM PYMT_DTL WHERE EXISTS (SELECT CLM_CASE_NO FROM TEMP_ARCHIVE1);


      I see rows only get deleted with 2nd query


      if both queries are same why is not 1st query deleteing rows ?

      Thanks in Advance
        • 1. Re: Delete with IN and EXISTS
          229023
          Your EXISTS query should have link to the parent table.
          DELETE FROM PYMT_DTL pd
          WHERE EXISTS (SELECT null
                                         FROM TEMP_ARCHIVE1 t1
                                         WHERE t1.CLM_CASE_NO = pd.CLM_CASE_NO);
          • 2. Re: Delete with IN and EXISTS
            Frank Kulash
            Hi,

            The two DELETE statements you posted are not the same.
            DELETE  FROM PYMT_DTL
            WHERE  EXISTS
                    ( SELECT CLM_CASE_NO
                      FROM TEMP_ARCHIVE1
                    );
            will see if there is anything at all in the temp_archive1 table. If so, it will delete every row in pymt_dtl. If not, nothing will be deleted.

            If you want to delete rows from pymt_dtl that have a matching row in temp_archive1, then you can use the first DELETE statement you posted, or this:
            DELETE  FROM pymt_dtl  m
            WHERE   EXISTS
                    (
                    SELECT  0
                    FROM    temp_archive1
                    WHERE   clm_case_no = m.clm_case_no
                    );
            • 3. Re: Delete with IN and EXISTS
              652108
              In two cases
              with IN clause and EXISTS clause

              I have to delete rows in both cases

              for IN case I am not deleting any rows ( there are matches on PYMT_DTL and TEMP_ARCHIVE1 tables)

              for Exists case I am deleeting rows.

              If both are same
              How is that I am seeing the Difference ?

              Thanks!
              • 4. Re: Delete with IN and EXISTS
                SomeoneElse
                If both are same
                How is that I am seeing the Difference ?
                It seems that has been explained. Have you not read your own thread?
                • 5. Re: Delete with IN and EXISTS
                  652108
                  Someonelse,

                  I got response in between posting message on thread so I couldn't read the thread.

                  My two cases are IN and Exists


                  I have to delete the rows from pymt_dtl if there is match in temp_archive1
                  so
                  What I have noticed is
                  Case 1 is IN
                  DELETE FROM PYMT_DTL WHERE CLM_CASE_NO IN
                  (SELECT CLM_CASE_NO FROM TEMP_ARCHIVE1 );


                  is not deleting any rows

                  Case 2 is EXISTS

                  DELETE FROM PYMT_DTL WHERE EXISTS (SELECT CLM_CASE_NO FROM TEMP_ARCHIVE1);
                  is deleting rows
                  If Case 1==Case 2
                  why is that Case 1 is not deleting rows ?
                  • 6. Re: Delete with IN and EXISTS
                    Frank Kulash
                    Hi,

                    Those two statements are not the same.
                    If the were the same, they would produce the same results.
                    As you demonstrated, they do not produce the same results.

                    In general, it is possible to use an EXISTS sub-query to get the same results as an IN subquery. Unlike your Case 2, the EXISTS sub-query has to be correllated to the main query, that is, something (usually the WITH-clause) refers to a value for the current row in the main query. Sundar and I have both given examples in this thread.
                    • 7. Re: Delete with IN and EXISTS
                      SomeoneElse
                      He says he can't read the thread. This might be a bug from the maintenance last weekend where you'll get a Null Pointer Exception in the middle of a thread. I've seen it a few times too.