CODEWITHSUNDEEP
sqlite
MRR0GERS
MRR0GERS

Reputation: 654

SQLite: Delete results from SELECT with EXCEPT

I've got two tables, Feeds and Import in an SQLite DB. I'm pulling all enclosures from a list of rss feeds into the Import table; most of which are in the Feeds table as well. I'm trying to delete the records from the Feeds table where the enclosures are not in the Import table because they are no longer in the rssfeeds and they've been downloaded. Basically this is my setup:

TABLE Feeds
    id (TEXT)
    url (TEXT)
    downloaded (BIT)

TABLE Import
    id (TEXT)
    url (TEXT)

# results to delete
(SELECT id,url FROM Feeds WHERE downloaded = 1 EXCEPT SELECT id,url FROM Import)

I'm probably over thinking this and making it way more complicated than it really is.

Upvotes: 3

Views: 9738

Answers (2)

dcp
dcp

Reputation: 55444

DELETE
  FROM Feeds
 WHERE downloaded = 1
   AND NOT EXISTS (
          SELECT *
            FROM Import b
           WHERE Feeds.id = b.id
             AND Feeds.url = b.url )

Upvotes: 5

Benoit
Benoit

Reputation: 79185

DELETE
  FROM Feeds
 WHERE downloaded = 1
   AND NOT EXISTS (SELECT 1 FROM Import WHERE id = Feeds.id AND url = Feeds.url)

Upvotes: 0

Related Questions