Search code examples
sqlsqlitejoinsql-delete

Deleting value using SQlite while doing an INNER JOIN

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

Solution

  • 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);