Delete from SQLite table with join to link table

Question

I have these tables:

items:

+ _id
+ timestamp

itemlists:

+ _id
+ timestamp

linktable:

+ _id
+ item_id foreign key references items(_id)
+ item_list_id foreign key references item_list(_id)

An item can be in many lists. A list can contain many items. It's a many-to-many relationship.

I now want to delete an itemlist and all items in it, if they are unique to that list. However I don't want to delete any item from items table if they are in another list as well.

I can retrieve all items for a specific playlist:

items a inner join item_list b on a._id = b.item_id where item_list_id = ?

But I'm struggling with how to structure a delete for a specific list and its items, without deleting items that are also referenced by another list that is not deleted.

Any pointers in the right direction are highly appreciated.

I'm running this on SQLite on Android.

if you want to delete an entire table can you not use dropTable? — Apurva
– Apurva, Commented Feb 5, 2015 at 7:29
Not an entire table. I want to delete a list from itemlists, and all items in items table if they are only in that list. If they are also in another list I can't delete them from the items table. — Pierre Rymiortz
– Pierre Rymiortz, Commented Feb 5, 2015 at 7:34

CL. · Accepted Answer · 2015-02-05 07:45:15Z

1

First, delete the list:

DELETE FROM itemlists WHERE _id = ?;

Then delete all its links:

DELETE FROM linktable WHERE item_list_id = ?;

Then delete all items that have no link left:

DELETE FROM items WHERE _id NOT IN (SELECT item_id FROM linktable);

Do all this in a single transaction.

answered Feb 5, 2015 at 7:45

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete from SQLite table with join to link table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related