Home » SQL & PL/SQL » SQL & PL/SQL » Delete all levels of employees those who are under BLAKE?
Delete all levels of employees those who are under BLAKE? [message #612195] Mon, 14 April 2014 06:38 Go to next message
kiranrathodkr916
Messages: 25
Registered: March 2014
Location: India
Junior Member
Delete all levels of employees those who are under BLAKE?

my solution

[code]delete from emp where empno in( select empno from emp start with ename='BLAKE' connect by prior emp1no=mgr)[/code]

6 rows deleted


given soln
Delete emp1 where ename in(select ename from emp1 start with emp1no = (select emp1no from emp1 where ename='BLAKE') 
connect by prior emp1no = mgr) 

6 rows deleted



how my soln different from given soln.are they both same?

[Updated on: Mon, 14 April 2014 06:39]

Report message to a moderator

Re: Delete all levels of employees those who are under BLAKE? [message #612196 is a reply to message #612195] Mon, 14 April 2014 06:42 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
What prevents you exactly from reading Hierarchical Queries ?
Re: Delete all levels of employees those who are under BLAKE? [message #612197 is a reply to message #612195] Mon, 14 April 2014 06:56 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1885
Registered: May 2013
Location: World Wide on the Web
Senior Member
kiranrathodkr916 wrote on Mon, 14 April 2014 17:08
how my soln different from given soln.are they both same?


What difference do you observe? Both would return same result. In your second query, there is an extra statement :

select emp1no from emp1 where ename='BLAKE'


Which could be simply replaced with
 where ename = 'BLAKE' 
And I see your 1st query is already doing that.

What's your actual concern?
Re: Delete all levels of employees those who are under BLAKE? [message #612200 is a reply to message #612197] Mon, 14 April 2014 07:12 Go to previous messageGo to next message
cookiemonster
Messages: 10860
Registered: September 2008
Location: Rainy Manchester
Senior Member
Unless ename is unique, and I doubt it is, the two are not equivalent and the 2nd one is wrong.
Re: Delete all levels of employees those who are under BLAKE? [message #612220 is a reply to message #612200] Mon, 14 April 2014 09:15 Go to previous message
Solomon Yakobson
Messages: 1967
Registered: January 2010
Senior Member
Both are wrong. Task was to delete all levels of employees those who are under BLAKE. Both solutions also delete Blake. So I'd use:

delete from emp where empno in(select empno from emp start with ename='BLAKE' connect by prior empno = mgr and level > 1)


SY.

Previous Topic: Remove the department in dept table where dept does not have any employees?
Next Topic: how can i insert 00-jan-2014 into a date column
Goto Forum:
  


Current Time: Fri Aug 01 18:05:31 CDT 2014

Total time taken to generate the page: 0.09394 seconds