CODEWITHSUNDEEP
mysql
user2481664
user2481664

Reputation: 43

Mysql delete row where parent does not exist

I have a table called items with collumns "itemID" and "parentID" where the parentID is an ID from another row in the table. I want to remove the rows where the parentID-row doesn't exist anymore.

This is what i came up with:

DELETE FROM items
WHERE (SELECT COUNT(1)
       FROM items as parent
       WHERE items.parentID=parent.itemID)=0

But i get the following error: You can't specify target table 'items' for update in FROM clause

EDIT: it would be nice if items linking to items removed by this query will also be removed by the query, is this possible in sql or should I code it?

Upvotes: 4

Views: 3575

Answers (3)

SQL.injection
SQL.injection

Reputation: 2647

try this little sorcery...

delete from FooTable f1 where f1.parent_id 
         not in (select f2.item_id from FooTable f2);

of course this query has a small problem, if you are sharpy you probably noticed, that it might generate new FooRecords without parent (this is, their parent might be deleted during the query execution).

Execute the query multiple times by hand, if you don't need something automated, or place it inside a loop and stop the loop when the number of deleted records is 0.

Upvotes: 0

Tomas
Tomas

Reputation: 59489

Using subqueries is inefficent; use left join with multiple-table syntax:

DELETE items 
FROM items
    left join items as parent 
        on items.parentID = parent.itemID
WHERE parent.itemID is NULL

multiple-table syntax allows you to specify join in the delete command - so you can join multiple tables and only delete from one. I.e. DELETE t1 FROM t1 join t2 .... will join t1 and t2 but only delete the corresponding rows from table t1.

As for your question "it would be nice if items linking to items removed by this query will also be removed by the query, is this possible in sql or should I code it?"

I think this would end up with cascade - the deleted record could be parent of another record, so delete that one also, but that one could be also parent of another record, so you need to delete it too, etc. etc. I think it is not possible to query all this cascade by a single SQL query.

So I think the easiest solution is to re-run the above query until no rows are deleted.

Upvotes: 7

Yogesh Suthar
Yogesh Suthar

Reputation: 30488

The query will be

DELETE items FROM items 
left join parent 
    on items.parentID = parent.ID
WHERE parent.ID is NULL

Upvotes: 3

Related Questions