mysql - Delete an SQL query result set

Question

I'm trying to delete an SQL result set but it won't work:

DELETE FROM votes
WHERE id IN (
SELECT *
FROM votes v
LEFT JOIN comments c ON f.id = v.post_id
GROUP BY v.id
HAVING COUNT(c.comment) = 0 )

Answer 1

It's true, that you can't use the same table from which you want to delete rows in a direct subselect, but with a little trick - a subselect on a subselect as derived table - you can do it:

DELETE FROM votes
WHERE id IN (
    SELECT 
        t.id
    FROM ( 
        SELECT v.id, COUNT(c.comment) cnt
        FROM votes v
        LEFT JOIN comments c ON f.id = v.post_id
        GROUP BY v.id
        HAVING COUNT(c.comment) = 0 
    ) t
);

I'm assuming that the rows without comments should be deleted.