473,325 Members | 2,860 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

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 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
5 1192
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

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
Thanks, John. That works great!

Jul 23 '05 #4

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

Jul 23 '05 #5
Look up the nested sets model for trees; this can be done without
procedural code.

Jul 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Jonan | last post by:
Hello, For several reasons I want to replace the built-in memory management with some custom built. The mem management itlsef is not subject to my question - it's ok to the point that I have...
2
by: Cornelius Buschka | last post by:
Hi, we saw the following problem: We deleted all rows from a table B referencing table A (~500000 records). No problem, but the following try to delete all records from table A (~180000) lead...
3
by: NateDawg | last post by:
I'm reposting this. I'm kinda in a bind untill i get this figured out, so if anyone has some input it would sure help me out. Ok, I’ve noticed a few gridview problems floating around the forum....
2
by: Bob Tinsman | last post by:
This problem shows up in Firefox 1.5.0.1 and Rhino 1.6R2. I've found that if I have an XML node expression that ends in a filter, I can't use it with the delete operator. In the following...
6
by: radishcarrot | last post by:
Hi, I am rather new to C++ and an invalid exception has occurs in my system which I have absolutely no idea as to why it happens. Please help! Thank you. I have created a method which takes in...
6
by: Lighter | last post by:
Big Problem! How to overload operator delete? According to C++ standard, "A deallocation function can have more than one parameter."(see 3.7.3.2); however, I don't know how to use an overloaded...
5
by: rn5a | last post by:
The .NET 2.0 documentation states the following: When using a DataSet or DataTable in conjunction with a DataAdapter & a relational data source, use the Delete method of the DataRow to remove...
9
by: rohits123 | last post by:
I have an overload delete operator as below ////////////////////////////////// void operator delete(void* mem,int head_type) { mmHead local_Head = CPRMemory::GetMemoryHead(head_type);...
4
by: =?Utf-8?B?UmljaA==?= | last post by:
On a form - I have a datagridview which is docked to the entire form. The datagridview allows users to Delete and/or Add Rows. On the Form_Load event I Fill the datagridview source table with a...
11
by: Ed Dror | last post by:
Hi there, I'm using ASP.NET 2.0 and SQL Server 2005 with VS 2005 Pro. I have a Price page (my website require login) with GridView with the following columns PriceID, Amount, Approved,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.