DELETE problem

I have a hierarchy of menu and sub menus setup using this table:
CREATE TABLE menu
(
id INT NOT NULL IDENTITY PRIMARY KEY,
name VARCHAR(30)NOT NULL,
parentID INTNULL --Null If Root Menu
)

I want all sub menus to be deleted when a parent menu is deleted. I
wasn't able to get a recursive procedure to work because I got an error
about multiple cursors w/ the same name.

The best I've come up w/ is this:

DELETE FROM menu WHERE id = x --Del Menu
--Cleanup Children
DELETE FROM menu WHERE parentID <> NULL AND parentID NOT IN(SELECT id
from menu)

Is there a better/faster way of doing this?Hi

If you did not see my reply to your previous post, the following is about
the best you can do

DELETE FROM menu WHERE id = x --Del Menu

WHILE @.@.ROWCOUNT > 0
BEGIN
DELETE FROM menu
WHERE parentID IS NOT NULL
AND parentID NOT IN (SELECT id FROM menu)
END

Do not use <> NULL

John

<wackyphill@.yahoo.com> wrote in message
news:1103306757.201621.73930@.z14g2000cwz.googlegro ups.com...
>I have a hierarchy of menu and sub menus setup using this table:
> CREATE TABLE menu
> (
> id INT NOT NULL IDENTITY PRIMARY KEY,
> name VARCHAR(30) NOT NULL,
> parentID INT NULL --Null If Root Menu
> )
> I want all sub menus to be deleted when a parent menu is deleted. I
> wasn't able to get a recursive procedure to work because I got an error
> about multiple cursors w/ the same name.
> The best I've come up w/ is this:
> DELETE FROM menu WHERE id = x --Del Menu
> --Cleanup Children
> DELETE FROM menu WHERE parentID <> NULL AND parentID NOT IN(SELECT id
> from menu)
> Is there a better/faster way of doing this?|||John Bell wrote:
> Hi
> If you did not see my reply to your previous post, the following is
about
> the best you can do
> DELETE FROM menu WHERE id = x --Del Menu
> WHILE @.@.ROWCOUNT > 0
> BEGIN
> DELETE FROM menu
> WHERE parentID IS NOT NULL
> AND parentID NOT IN (SELECT id FROM menu)
> END
> Do not use <> NULL
> John
> <wackyphill@.yahoo.com> wrote in message
> news:1103306757.201621.73930@.z14g2000cwz.googlegro ups.com...
> >I have a hierarchy of menu and sub menus setup using this table:
> > CREATE TABLE menu
> > (
> > id INT NOT NULL IDENTITY PRIMARY KEY,
> > name VARCHAR(30) NOT NULL,
> > parentID INT NULL --Null If Root Menu
> > )
> > I want all sub menus to be deleted when a parent menu is deleted. I
> > wasn't able to get a recursive procedure to work because I got an
error
> > about multiple cursors w/ the same name.
> > The best I've come up w/ is this:
> > DELETE FROM menu WHERE id = x --Del Menu
> > --Cleanup Children
> > DELETE FROM menu WHERE parentID <> NULL AND parentID NOT IN(SELECT
id
> > from menu)
> > Is there a better/faster way of doing this?|||Thanks, John. That works great!|||Sorry John I did miss your last post and you are right, that works very
well. Thank you very much.|||Look up the nested sets model for trees; this can be done without
procedural code.