SQL 根据另一个表删除一个表中的所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1590799/
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 all rows in a table based on another table
提问by mrblah
I can't seem to ever remember this query!
我似乎不记得这个查询！
I want to delete all rows in table1 whose ID's are the same as in Table2.
我想删除 table1 中 ID 与 Table2 相同的所有行。
So:
所以：
DELETE table1 t1
WHERE t1.ID = t2.ID
I know I can do a WHERE ID IN (SELECT ID FROM table2) but I want to do this query using a JOIN if possible.
我知道我可以在 WHERE ID IN (SELECT ID FROM table2) 中执行此查询，但如果可能，我想使用 JOIN 执行此查询。
回答by Stephen Mesa
DELETE Table1
FROM Table1
INNER JOIN Table2 ON Table1.ID = Table2.ID
回答by HLGEM
DELETE t1
FROM Table1 t1
JOIN Table2 t2 ON t1.ID = t2.ID;
I always use the alias in the delete statement as it prevents the accidental
我总是在删除语句中使用别名，因为它可以防止意外
DELETE Table1
caused when failing to highlight the whole query before running it.
在运行之前未能突出显示整个查询时导致。
回答by C?t?lin Piti?
There is no solution in ANSI SQL to use joins in deletes, AFAIK.
ANSI SQL 中没有在删除中使用连接的解决方案，AFAIK。
DELETE FROM Table1
WHERE Table1.id IN (SELECT Table2.id FROM Table2)
Later edit
稍后编辑
Other solution (sometimes performing faster):
其他解决方案（有时执行速度更快）：
DELETE FROM Table1
WHERE EXISTS( SELECT 1 FROM Table2 Where Table1.id = Table2.id)
回答by Vesanto
PostgreSQL implementation would be:
PostgreSQL 实现将是：
DELETE FROM t1
USING t2
WHERE t1.id = t2.id;
回答by Yannick Motton
Try this:
尝试这个：
DELETE Table1
FROM Table1 t1, Table2 t2
WHERE t1.ID = t2.ID;
or
或者
DELETE Table1
FROM Table1 t1 INNER JOIN Table2 t2 ON t1.ID = t2.ID;
回答by bogertron
I think that you might get a little more performance if you tried this
我认为如果你尝试这个，你可能会得到更多的表现
DELETE FROM Table1
WHERE EXISTS (
SELECT 1
FROM Table2
WHERE Table1.ID = Table2.ID
)
回答by More
This will delete all rows in Table1that match the criteria:
这将删除Table1符合条件的所有行：
DELETE Table1
FROM Table2
WHERE Table1.JoinColumn = Table2.JoinColumn And Table1.SomeStuff = 'SomeStuff'
回答by Ankur Gupta
Found this linkuseful
发现这个链接很有用
Copied from there
从那里复制的
Oftentimes, one wants to delete some records from a table based on criteria in another table. How do you delete from one of those tables without removing the records in both table?
通常，人们希望根据另一个表中的条件从表中删除一些记录。如何从其中一个表中删除而不删除两个表中的记录？
DELETE DeletingFromTable
FROM DeletingFromTable INNER JOIN CriteriaTable
ON DeletingFromTable.field_id = CriteriaTable.id
WHERE CriteriaTable.criteria = "value";
The key is that you specify the name of the table to be deletedfrom as the SELECT. So, the JOIN and WHERE do the selection and limiting, while the DELETE does the deleting. You're not limited to just one table, though. If you have a many-to-many relationship (for instance, Magazines and Subscribers, joined by a Subscription) and you're removing a Subscriber, you need to remove any potential records from the join model as well.
关键是您指定要从中删除的表的名称作为 SELECT。因此，JOIN 和 WHERE 进行选择和限制，而 DELETE 进行删除。但是，您不仅限于一张桌子。如果您具有多对多关系（例如，杂志和订阅者，通过订阅加入）并且您要删除订阅者，则还需要从联接模型中删除任何潜在记录。
DELETE subscribers, subscriptions
FROM subscribers INNER JOIN subscriptions
ON subscribers.id = subscriptions.subscriber_id
INNER JOIN magazines
ON subscriptions.magazine_id = magazines.id
WHERE subscribers.name='Wes';
Deleting records with a join could also be done with a LEFT JOIN and a WHERE to see if the joined table was NULL, so that you could remove records in one table that didn't have a match (like in preparation for adding a relationship.) Example post to come.
使用连接删除记录也可以使用 LEFT JOIN 和 WHERE 来完成，以查看连接表是否为 NULL，以便您可以删除一个表中没有匹配的记录（例如为添加关系做准备。 ) 示例帖子来了。
回答by Frédéric
Since the OP does not ask for a specific DB, better use a standard compliant statement.
Only MERGEis in SQL standard for deleting (or updating) rows while joining something on target table.
由于 OP 不要求特定的数据库，因此最好使用符合标准的语句。仅MERGE在 SQL 标准中用于在加入目标表上的某些内容时删除（或更新）行。
merge table1 t1
using (
select t2.ID
from table2 t2
) as d
on t1.ID = d.ID
when matched then delete;
MERGEhas a stricter semantic, protecting from some error cases which may go unnoticed with DELETE ... FROM. It enforces 'uniqueness' of match : if many rows in the source (the statement inside using) match the same row in the target, the merge must be canceled and an error must be raised by the SQL engine.
MERGE具有更严格的语义，可防止某些错误情况可能会被DELETE ... FROM. 它强制匹配的“唯一性”：如果源（内部语句using）中的许多行与目标中的同一行匹配，则必须取消合并，并且 SQL 引擎必须引发错误。
回答by Rob
While the OP doesn't want to use an 'in' statement, in reply to Ankur Gupta, this was the easiest way I found to delete the records in one table which didn't exist in another table, in a one to many relationship:
虽然 OP 不想使用“in”语句，但在回复 Ankur Gupta 时，这是我发现删除另一个表中不存在的表中记录的最简单方法，在一对多关系中：
DELETE
FROM Table1 as t1
WHERE ID_Number NOT IN
(SELECT ID_Number FROM Table2 as t2)
Worked like a charm in Access 2016, for me.
对我来说，在 Access 2016 中的工作就像一个魅力。
