我试图从选民表中删除所有选民,他们没有登记为民主党人或共和党人,并且只投过一次票。我有一个包含三个表的数据库,congress_members、voters 和 votes,并且必须与选民联合投票才能删除正确的数据。
此代码找到我要删除的数据:
SELECT voters.*
FROM voters JOIN votes ON voters.id = votes.voter_id
WHERE party = 'green' OR party = 'na' OR party = 'independent'
GROUP BY votes.voter_id
HAVING COUNT(*) = 1;
但是我无法删除它,因为每次尝试使用 JOIN 语句删除时都会出错
最佳答案
您可以将其表述为带有 delete
子句的 where
:
delete from voters
where votes.party not in ('democrat', 'republican') and
voters.id in (select id from votes group by id having count(*) = 1);