SQL 从第二个表中存在匹配项的表中删除行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14794028/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete row from table where match exists in second table
提问by amjo324
I have Table A with the following values:
我的表 A 具有以下值：
+------+------+ | ID1 | ID2 | +------+------+ | 1689 | 1709 | | 1709 | 1689 | | 1782 | 1709 | | 1911 | 1247 | | 1247 | 1468 | | 1641 | 1468 | | 1316 | 1304 | | 1501 | 1934 | | 1934 | 1501 | | 1025 | 1101 | +------+------+
and another relation (Table B) with the following values:
以及具有以下值的另一个关系（表 B）：
+------+------+ | ID1 | ID2 | +------+------+ | 1641 | 1468 | | 1911 | 1247 | +------+------+
I would like to delete all rows in Table A that appear in Table B (an exact match on ID1 and ID2). Seems simple in theory but I'm having no joy with the EXISTS statement or other approaches. I'm using SQLite.
我想删除表 A 中出现在表 B 中的所有行（与 ID1 和 ID2 完全匹配）。理论上看起来很简单，但我对 EXISTS 语句或其他方法不满意。我正在使用 SQLite。
Any suggestions greatly appreciated.
任何建议都非常感谢。
回答by Bernhard Barker
How about: (not too sure whether this works in SQLite)
怎么样：（不太确定这是否适用于 SQLite）
DELETE FROM TableA
WHERE EXISTS (SELECT *
FROM TableB
WHERE TableB.ID1 = TableA.ID1
AND TableB.ID2 = TableA.ID2)
回答by Markus
In MSSQL you can do this: (most efficient)
在 MSSQL 中，您可以这样做：（最有效）
DELETE a FROM a JOIN b ON a.ID1 = b.ID1 AND a.ID2 = b.ID2
DELETE a FROM a JOIN b ON a.ID1 = b.ID1 AND a.ID2 = b.ID2
回答by Ridwan Galib
DELETE a FROM TableA a INNER JOIN TableB b ON a.ID1=b.ID1 AND a.ID2=b.ID2
