在SQLite表中删除没有匹配项的行，如何操作？

Question

我需要从一个SQLite表中删除行，这些行的行ID在另一张表中不存在。SELECT语句可以返回正确的行：

SELECT * FROM cache LEFT JOIN main ON cache.id=main.id WHERE main.id IS NULL;

然而，删除语句在SQLite中会生成一个错误：

DELETE FROM cache LEFT JOIN main ON cache.id=main.id WHERE main.id IS NULL;

错误为：SQLite 错误1 - near "left": 语法错误。我是否可以使用其他语法？

- Marek Jedliński

---

为了解释清楚：我正在使用额外的where条件从“主”表中删除行，以保留用户标记为“锁定”的任何行（即，在解锁之前不应删除的行）：DELETE FROM main WHERE id = ? AND locked = 0;每次成功删除后，必须删除“缓存”表中匹配的行，但是SQlite没有返回任何值，让我知道第一个删除语句是否实际匹配了任何行。因此，我尝试从缓存表中删除“不匹配”的行，并卡在了左连接上。 - Marek Jedliński

Answer 1

显然SQLite不支持在删除语句中使用join，正如您可以在语法图表中看到的那样。但是，您应该能够使用子查询来删除它们。

例如：

DELETE FROM cache WHERE id IN
(SELECT cache.id FROM cache LEFT JOIN main ON cache.id=main.id WHERE main.id IS NULL);

（未经测试）

- wimvds

---

谢谢！在看到你的答案之前，我已经接近了这个问题，只是我使用的是等于号而不是IN，所以我的尝试并没有完全达到预期的效果。 - Marek Jedliński

Answer 2

既然您选择使用子查询，那么干脆完全摒弃连接并简化查询：

DELETE FROM cache WHERE id NOT IN (SELECT id from main);

- Igor Zevaka

---

如果 main 包含数百万行数据，则这不是一个理想的解决方案。最好先进行连接操作。 - Jay

@Jacob 希望没有人首先在 SQLite 中处理数百万行。 - fluffy

如果您的表格没有主键，您可以临时创建一个，例如：DELETE FROM cache WHERE x || ',' || y NOT IN (SELECT x || ',' || y from main); - bonh

@fluffy，不要低估SQLite :) 它完全可以处理数百万行数据，但即使只有几千行，这个SELECT id FROM查询也是无端地低效和缓慢的。@winvds的答案是一个更好的选择。 - laurent

DELETE FROM cache WHERE NOT EXISTS (SELECT main.id from main WHERE main.id=cache.id) - Brad Robinson

Answer 3

@wimvds的解决方案对我没有用，所以我修改了查询并删除了WHERE条件：

DELETE FROM FirstTable WHERE firstTableId NOT IN (SELECT SecondTable.firstTableId FROM SecondTable LEFT JOIN FirstTable ON FirstTable.firstTableId=SecondTable.firstTableId)

这将删除FirstTable中所有未分配id给SecondTable任何行的行。

- Micer