CODEWITHSUNDEEP
mysqlsql-delete
Bernie Davies
Bernie Davies

Reputation: 419

Delete from table A where no children exist in table A and no children exist in table B

I have a traditional parent/child table for simple tree categorisation and a 'page' table for pages linked to the categories:

    **Table: cat**
    c_id
    c_name
    parent_id

    **Table: page**
    p_id
    page_name
    c_id

I would like to perform a deletion only if there are no child categories in the cat table and no child pages in the page table. I know how to do this by counting and checking first. But, want to know if it can be achieved elegantly in a single query and get the affected rows.

I've tried this and it doesn't work as I think it is disallowed to select from the target table in the delete query?

    DELETE 
    FROM cat
    WHERE c_id=x
    AND NOT EXISTS (
    SELECT count( DISTINCT p_id ) as pages                       
    FROM page
    WHERE c_id =x
    )
    AND NOT EXISTS (
    SELECT count( DISTINCT c_id ) as children                       
    FROM cat
    WHERE parent_id =x
    );

Upvotes: 0

Views: 237

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 181027

A DELETE JOIN will work, even when deleting from a joined table. Here we LEFT JOIN to check if there are any connections (c2 is a possible parent, and p is a possible referring page)

Finally we just DELETE the rows where there are no connections (ie the connections return as NULL);

DELETE c1
FROM cat c1
LEFT JOIN cat c2 
  ON c1.c_id = c2.parent_id
LEFT JOIN page p
  ON c1.c_id = p.c_id
WHERE c2.parent_id IS NULL
 AND p.c_id IS NULL
 AND c1.c_id = 1

An SQLfiddle to test with.

Upvotes: 1

Related Questions