sqlite - 如何从SQLite的表中删除多行

Question

我有一个子查询，因为：

SELECT INTER1.LID1 AS ILID1,INTER1.LID2 AS ILID2 FROM
(SELECT L1.ID1 AS LID1, L1.ID2 AS LID2 FROM Friend F1
INNER JOIN Likes L1
ON F1.ID1 = L1.ID1 AND F1.ID2 = L1.ID2) INTER1
LEFT JOIN Likes L2
ON (INTER1.LID1 = L2.ID2 AND INTER1.LID2 = L2.ID1)
WHERE (L2.ID1 IS NULL AND L2.ID2 IS NULL)

其输出如下：

     ID1    ID2

    1641    1468
    1911    1247

现在，我必须从表LIKES删除这两个行，其格式如下：

  ID1       ID2

 1689       1709
 1709       1689
 1782       1709
 1911       1247
 1247       1468
 1641       1468

我尝试使用SQLite中的以下查询来完成此操作：

DELETE FROM Likes 
WHERE EXISTS 
(SELECT INTER1.LID1 AS ILID1,INTER1.LID2 AS ILID2 FROM
(SELECT L1.ID1 AS LID1, L1.ID2 AS LID2 FROM Friend F1
INNER JOIN Likes L1
ON F1.ID1 = L1.ID1 AND F1.ID2 = L1.ID2) INTER1
LEFT JOIN Likes L2
ON (INTER1.LID1 = L2.ID2 AND INTER1.LID2 = L2.ID1)
WHERE (L2.ID1 IS NULL AND L2.ID2 IS NULL));

但是它没有按预期工作，不是仅删除那些行。

然后，我尝试使用INNER JOIN解决此问题，但是SQLite中不存在与DELETE函数关联的INNER JOIN功能。

我该如何进行这项工作。

请帮助

Answer 1

只需一次比较两个值：

DELETE FROM Likes
WHERE (ID1, ID2) IN (SELECT ... your original query ...);

Row values需要SQLite 3.15或更高版本。
如果使用的是较早版本，则必须在单独的步骤中查找要删除的行，并使用单列主键（ rowid或声明为INTEGER PRIMARY KEY的任何键）：

DELETE FROM Likes
WHERE rowid IN (SELECT Likes.rowid
                FROM Likes
                JOIN (SELECT ... your original query ...) AS IDs
                  ON Likes.ID1 = IDs.ILID1
                 AND Likes.ID2 = IDs.ILID2);