Delete Related Record in same table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Delete Related Record in same table

Author

Topic

siva123
Starting Member

1 Post

Posted - 2005-07-18 : 06:19:53

Hi,
I have table called Categories with following filed (CategoryID, CategoryName, Parent).
How can I delete related record in the same table.

Records like
Id Name Parent
1 Main 0
2 Level 1 1
3 Level 2 2
4 Level 3 3

When I delte Record id 1 it shoud remove all related level records.

Can some one help me out this

Many thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-18 : 06:48:49

This may help you
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=43485

Madhivanan

Failing to plan is Planning to fail

giovi2002
Starting Member

46 Posts

Posted - 2005-07-18 : 16:23:27

I would guess make a synonym table and put it in a trigger.

For instance:
CREATE TRIGGER trg_deleteparent ON dbo.CATEGORIES
AS
AFTER DELETE

DELETE FROM CATEGORIES
WHERE PARENTID IN
(Select parentid
from categories As categories2
where Id= (Select Id From deleted))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources