Deletion of found data-CodePudding

I search for duplicate records in the table as follows:

SELECT a.*
FROM rss_item a
JOIN (SELECT title, feedurl, COUNT(*)
FROM rss_item 
WHERE feedurl LIKE '%blabla%'
GROUP BY title
HAVING count(*) > 1) b
ON a.title = b.title
AND a.feedurl = b.feedurl
WHERE a.guid NOT LIKE '%blabla%'

it works fine and finds exactly what I need to remove; but I don't understand how to remove these results from the table now. I know it should be something like DELETE FROM rss_item ... but I don't know how to implement it correctly. How to do it?

CodePudding user response：

Use a nested SELECT in combination with your DELETE :

DELETE FROM table
WHERE table.id IN (
    SELECT id FROM table
);

In your case:

DELETE FROM rss_item
WHERE rss_item.id IN (
    SELECT a.id
    FROM rss_item a
    JOIN (SELECT title, feedurl, COUNT(*)
    FROM rss_item 
    WHERE feedurl LIKE '%blabla%'
    GROUP BY title
    HAVING count(*) > 1) b
    ON a.title = b.title
    AND a.feedurl = b.feedurl
    WHERE a.guid NOT LIKE '%blabla%'
);