进行INNER JOIN时使用SQlite删除值 [英] Deleting value using SQlite while doing an INNER JOIN

问题描述

我正在尝试从选民表中删除所有选民，在这些选民表中，他们没有注册为民主党或共和党人，而只投票了一次.我有一个包含三个表，congress_members，选民和票数的数据库，并且必须与选民一起加入选票，才能删除正确的数据.

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;

但是我无法删除它，因为每次尝试使用JOIN语句删除时都会出现错误

But I am unable to delete it because I am getting an error everytime I try to delete with a JOIN statement

推荐答案

您可以使用where子句将此短语表达为delete:

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

这篇关于进行INNER JOIN时使用SQlite删除值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！