I am trying to delete all voters from a voters table where they are not registered as a democrat or republican AND only voted once. I have a database with three tables, congress_members, voters, and votes and have to JOIN votes with voters in order to delete the right data.
This code finds the data I want to delete:
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;
But I am unable to delete it because I am getting an error everytime I try to delete with a JOIN statement
You can phrase this as a delete
with a where
clause:
delete from voters
where votes.party not in ('democrat', 'republican') and
voters.id in (select id from votes group by id having count(*) = 1);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With