CODEWITHSUNDEEP
androidsqlsqlite
Vincent Mimoun-Prat
Vincent Mimoun-Prat

Reputation: 28561

Deleting rows that have no associated row in another table

I have 2 tables with a few columns: 

parent (id, name, a) 
child (id, parent_id, name)

I cannot manage to find the proper SQL query to delete all parents that have no children and a<10. This is for SQLite shipped with Android 2.1. Anybody could help?

Upvotes: 1

Views: 173

Answers (2)

Sigersted
Sigersted

Reputation: 356

Another way of doing it:

delete from parent
where a < 10
and id not in (select parent_id from child, parent b where child.parent_id = b.id)

Upvotes: 0

Andomar
Andomar

Reputation: 238226

delete from parent
where a < 10
      and not exists (select * from child where parent.id = child.parent_id)

Upvotes: 3

Related Questions