Delete Parent record if child is not present

Question

I am creating menu and submenus in a table variable. ( Typical parent child records in the same table) ParentID coll is null for all Top menus. And for their child ParentID will be the MenuId of these parent menus. ( Depending on what kind of user logs this is build ) Now what I have to do is if particular parent doesn't have any child I want to delete this parent recrod from this table variable.
I know I can to it with cursor or by row by row operation. Can I do it any other way?

Answer 1

A single statement like this should do it (as an alternative to using exists):

delete p
from
    tblMenu p
    left join tblMenu c on p.ID = c.ParentID
where 
    p.ParentID is null --Ensures that the item to delete is at the top
    and c.ParentID is null --Finds top level items with no children

Answer 2

Without seeing your table structure it's difficult to tell you the exact query you would need but, if I understand your question correctly, you just need to do something like this:

DELETE T 
FROM MyTable T 
WHERE NOT EXISTS(SELECT * FROM MyTable WHERE ParentID = T.MenuID)
    AND T.ParentID IS NULL

This query does a correlated subquery to find all the menu records that don't have child records. It uses SQL's EXISTS clause

Answer 3

You could do something like this:

delete from yourtable yt where not exists (select * from yourtable yt2 where yt.id = yt2.parentid) and yt.parentid is null