Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 759
darkphantom500 Avatar asked Dec 11 '22 11:12

darkphantom500


1 Answers

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);
like image 51
Gordon Linoff Avatar answered Jan 13 '23 13:01

Gordon Linoff