By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,829 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

DELETE problem

P: n/a
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?

Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

<wa********@yahoo.com> wrote in message
news:11*********************@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?

Jul 23 '05 #2

P: n/a

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

<wa********@yahoo.com> wrote in message
news:11*********************@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?


Jul 23 '05 #3

P: n/a
Thanks, John. That works great!

Jul 23 '05 #4

P: n/a

Sorry John I did miss your last post and you are right, that works very
well. Thank you very much.

Jul 23 '05 #5

P: n/a
Look up the nested sets model for trees; this can be done without
procedural code.

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.