开发者

SQLite: Delete results from SELECT with EXCEPT

开发者 https://www.devze.com 2023-01-18 10:23 出处:网络
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 th

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 an开发者_StackOverflowd making it way more complicated than it really is.


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


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

精彩评论

暂无评论...
验证码 换一张
取 消