Delete an SQL query result set

问题:

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 )

回答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.

回答2:

You are close...2 changes

• a subquery in a where in() statement can only return one field. Change select * to select v.id

• having count = 0 doesn't quite work in a logical sense. If count = 0 then it's not there to delete anyway. I suspect with the left join syntax you've used, you are going for votes that have 0 comments? Right idea with the left join, but you want where c.comment is null (left join produces nulls...where c.comment is null means there was no comment found).

Of course this won't work due to mysql:

DELETE FROM votes
WHERE id IN (
SELECT v.id
FROM votes v
LEFT JOIN comments c ON f.id = v.post_id
where c.comments is null)

If I was stuck in MySQL...(sorry this is psuedo code, I haven't been in mysql long enough to get this exact in a text window.

select id
into #temp
FROM votes v
LEFT JOIN comments c ON f.id = v.post_id
where c.comments is null

delete from votes where id in (select id from #temp)
drop table #temp

Seems like a silly work around