OraFAQ Forum: SQL & PL/SQL » Delete all levels of employees those who are under BLAKE?

Home » SQL & PL/SQL » SQL & PL/SQL » Delete all levels of employees those who are under BLAKE?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Delete all levels of employees those who are under BLAKE? [message #612195]

Mon, 14 April 2014 06:38

kiranrathodkr916
Messages: 36
Registered: March 2014
Location: India

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

dariyoosh
Messages: 538
Registered: March 2009
Location: France

Senior Member

What prevents you exactly from reading Hierarchical Queries ?

Report message to a moderator

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

Lalit Kumar B
Messages: 3174
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?

Report message to a moderator

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

cookiemonster
Messages: 13920
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.

Report message to a moderator

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

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

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.

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 06:10:08 CDT 2024