7 Replies Latest reply: Mar 20, 2012 8:23 AM by Oracle_Walker RSS

    Delete query using inner join

    Oracle_Walker
      I need to delete records from a table using inner join from other tables. See below for the table structure and records.
      CREATE TABLE STUDENT (ROLL_NO NUMBER(10),NAME VARCHAR2(100),DEPT_NO NUMBER(10),JOIN_DATE DATE);
      CREATE TABLE DEPART (DEPT_NO NUMBER(10), DEPT_NAME VARCHAR2(100));
      CREATE TABLE ACTIVE_DEPT (DEPT_NO NUMBER(10), IS_ACTIVE VARCHAR2(1));
      
      INSERT INTO STUDENT (ROLL_NO,NAME,DEPT_NO,JOIN_DATE) VALUES(1,'SAM',10,SYSDATE-10);
      INSERT INTO STUDENT (ROLL_NO,NAME,DEPT_NO,JOIN_DATE) VALUES(2,'ALEX',10,SYSDATE -10);
      INSERT INTO STUDENT (ROLL_NO,NAME,DEPT_NO,JOIN_DATE) VALUES(3,'FRANK',20,SYSDATE -10);
      INSERT INTO STUDENT (ROLL_NO,NAME,DEPT_NO,JOIN_DATE) VALUES(4,'WAYNE',20,SYSDATE+1);
      INSERT INTO STUDENT (ROLL_NO,NAME,DEPT_NO,JOIN_DATE) VALUES(5,'KRUL',30,SYSDATE -10);
      INSERT INTO STUDENT (ROLL_NO,NAME,DEPT_NO,JOIN_DATE) VALUES(6,'ALICE',40,SYSDATE -10);
      
      INSERT INTO DEPART (DEPT_NO,DEPT_NAME) VALUES (10,'DEPT1');
      INSERT INTO DEPART (DEPT_NO,DEPT_NAME) VALUES (20,'DEPT2');
      INSERT INTO DEPART (DEPT_NO,DEPT_NAME) VALUES (30,'DEPT3');
      
      INSERT INTO ACTIVE_DEPT (DEPT_NO,IS_ACTIVE) VALUES (10,'Y');
      INSERT INTO ACTIVE_DEPT (DEPT_NO,IS_ACTIVE) VALUES (20,'N');
      INSERT INTO ACTIVE_DEPT (DEPT_NO,IS_ACTIVE) VALUES (30,'Y');
      
      DELETE FROM STUDENT WHERE (STUDENT.DEPT_NO) IN (SELECT D.dept_no FROM DEPART D,ACTIVE_DEPT AD WHERE D.DEPT_NO = AD.DEPT_NO AND AD.IS_ACTIVE = 'N');
      The above delete query will delete two records from the STUDENT table. Now i want to include another condition say, join_date should be a past date (join_date < trunc(sysdate)).
      How to include this condition in the above delete statement. I know i can do this using a subquery but i wont prefer that option. Basically i don't have much idea about join in delete statements.
      Please help.
        • 1. Re: Delete query using inner join
          Lokanath Giri
          Is this you need ?
          DELETE FROM student WHERE join_date < TRUNC(SYSDATE)
                                                  AND student.dept_no IN (SELECT D.dept_no
                                                                            FROM depart d,active_dept ad
                                                                           WHERE d.dept_no = ad.dept_no AND ad.is_active = 'N');
          Only one record is deleted. -> 3,'FRANK',20,SYSDATE -10


          >
          Balaji      wrote:
          Yes. But here we are getting the dept_no from the sub query, is there a better way to achieve this without sub query?
          >

          As far I know you need to have sub query in this case.

          You can close this thread if answered. If you have any further queries please post it so that someone can help you.

          Edited by: Lokanath Giri on २० मार्च, २०१२ ५:०५ अपराह्न
          • 2. Re: Delete query using inner join
            Oracle_Walker
            Yes. But here we are getting the dept_no from the sub query, is there a better way to achieve this without sub query?
            • 3. Re: Delete query using inner join
              Oracle_Walker
              Thank you..
              • 4. Re: Delete query using inner join
                chris227
                subquery is the right way.

                However, there is no need for the join in the subquery, since all departments are already in the active_dept table.

                so it would be sufficent to write
                DELETE FROM STUDENT
                WHERE (STUDENT.DEPT_NO)
                   IN (SELECT dept_no FROM ACTIVE_DEPT AD WHERE AD.IS_ACTIVE = 'N');
                Going further, i would say, that is could be simpler to add the active column to the dept table directly.

                regards
                • 5. Re: Delete query using inner join
                  Oracle_Walker
                  I have one more doubt here, say i want to filter the records from joining another table with the result set returned from the sub query. how can i do that?
                  • 6. Re: Delete query using inner join
                    Lokanath Giri
                    You can have multiple levels & final query should give an out put of list of dept_no
                    WHERE (STUDENT.DEPT_NO)
                       IN (SELECT dept_no FROM 
                                  ( SELECT dept_no FROM 
                                          (
                                             SELECT dept_no FROM <your table> 
                                             --Joins with other table 
                                          )
                                 )       --Joins with other table 
                                 --Joins with other table 
                           );
                    Balaji wrote:      
                    So you are saying to have multiple sub queries based on the requirement. does it affect the overall execution of a job when i am executing a delete statement using sub queries.
                    >

                    It was just an illustration you need to optimize the query always.

                    Edited by: Lokanath Giri on २० मार्च, २०१२ ६:५९ अपराह्न
                    • 7. Re: Delete query using inner join
                      Oracle_Walker
                      So you are saying to have multiple sub queries based on the requirement. does it affect the overall execution of a job when i am executing a delete statement using sub queries.