This content has been marked as final. Show 7 replies
Is this you need ?
Only one record is deleted. -> 3,'FRANK',20,SYSDATE -10
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');
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 २० मार्च, २०१२ ५:०५ अपराह्न
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
Going further, i would say, that is could be simpler to add the active column to the dept table directly.
DELETE FROM STUDENT WHERE (STUDENT.DEPT_NO) IN (SELECT dept_no FROM ACTIVE_DEPT AD WHERE AD.IS_ACTIVE = 'N');
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 );
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 २० मार्च, २०१२ ६:५९ अपराह्न