Deleting a value using SQlite while executing an INNER JOIN

Question

I try to remove all voters from the voters table, where they are not registered as a Democrat or a Republican, And I only voted once. I have a database with three tables, congress_members, voters and votes, and I need to JOIN the votes with voters to delete the correct 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 can’t delete it, because every time I try to delete using the JOIN operator, I get an error

Answer 1

You can express it as 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); 

Answer 2

You receive an error message because the connection will query your database and create a temporary table in which your new requested data will be stored. Delete items are used to delete data stored in your database on your disk, and not inside your memory.

The syntax for the delete instruction is "DELETE FROM table WHERE". The value of the table should be one of the three tables in your database, and your goal is voters. At the moment, you have half of your delete statement.

The where clause must be evaluated to a boolean value for each row. There is a function called EXISTS (). This function can be used to delete this data. Essentially, you will place your select statement from your message inside EXISTS (). The function will compare each of your rows in the target delete table with a row in your table inside the existing one. If there is a match, then the row exists, the function evaluates to true for that row and is deleted.

DELETE FROM voters WHERE (party = 'green' OR party = 'na' OR party = 'independent') AND EXISTS ( SELECT 1 FROM votes WHERE votes.id = voters.id HAVING COUNT(*) = 1 )