基于连接条件的 SQL 删除
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4097260/
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
SQL Delete based on condition in join
提问by Greg Dougherty
It is possible to delete records based on a satisfied condition with a join query?
是否可以使用连接查询根据满足的条件删除记录？
For instance, I have a linking table joining 3 records. The query I have at the moment deletes records from this table where one of the id's isn't IN()an imploded Php array. I've come to realise that the query should only remove records from this table if the id's don't exist in the array and they belong to a certain other table based on the a link to another table.
例如，我有一个连接 3 条记录的链接表。我目前的查询从该表中删除记录，其中一个 id 不是内IN()爆的 Php 数组。我已经意识到，如果 id 不存在于数组中，并且基于到另一个表的链接，它们属于某个其他表，则查询应该只从该表中删除记录。
采纳答案by Bill
I like to use EXISTS clauses for this:
我喜欢为此使用 EXISTS 子句：
DELETE FROM TableA
WHERE
<<put your array condition here>>
AND NOT EXISTS
(SELECT 1 FROM TableB Where TableB.ID=TableA.ID)
回答by Greg Dougherty
For SQL Server, the command is slightly different:
对于 SQL Server，命令略有不同：
DELETE FROM TableA
FROM TableA LEFT OUTER JOIN TableB ON TableA.Column = TableB.Column
WHERE TableB.Column IS NULL
No, that's not a typo, yes, you doneed "FROM TableA" twice. At least, you need the second FROM (the first is optional). The following has the advantage that it works for both SQL Server and MySQL:
不，这不是打字错误，是的，您确实需要两次“FROM TableA” 。至少，您需要第二个 FROM（第一个是可选的）。以下优点适用于 SQL Server 和 MySQL：
DELETE TableA
FROM TableA LEFT OUTER JOIN TableB ON TableA.Column = TableB.Column
WHERE TableB.Column IS NULL
回答by Ardalan Shahgholi
You can use :
您可以使用 ：
DELETE Based on a Join:
基于连接的 DELETE：
DELETE A
FROM TableA AS A
LEFT OUTER JOIN TableB As B ON A.Id = B.TabaleAId
WHERE B.Column IS NULL
Delete With SubQuery:
使用子查询删除：
DELETE
FROM TableA AS A
Where
A.id not in ( Select B.TabaleAId From Tab;eB As B )
or
或者
DELETE FROM TableA
WHERE Not EXISTS
(
SELECT *
FROM TableB As B
Where B.TableAId = TableA.Id
)
DELETE Using Table Expressions:
使用表表达式删除：
With A
As
(
Select TableA.*
FROM TableA AS A
LEFT OUTER JOIN TableB As B ON A.Id = B.TabaleAId
WHERE B.Column IS NULL
)
Delete From A
回答by JNK
DELETE FROM TableA
LEFT OUTER JOIN TableB
WHERE TableB.Column IS NULL
Will delete the records in tableA that don't have a corresponding record in TableB. Is that like what you are after?
将删除tableA中在TableB中没有对应记录的记录。这就是你所追求的吗？
回答by Chintan P
DELETE FROM a
FROM TableA AS a LEFT OUTER JOIN TableB AS b
on a.CALENDAR_DATE = b.CALENDAR_DATE AND a.ID = b.ID
Where b.ID is null
You can first use the select statement and verify your records that you want to delete and then remove the select statement and add Delete FROM tablename with the above query syntax.
您可以首先使用 select 语句并验证您要删除的记录，然后删除 select 语句并使用上述查询语法添加 Delete FROM tablename。
回答by Vahid Farahmandian
The easiest way to Delete based on join is as follow:
基于连接删除最简单的方法如下：
1.Write your query using SELECTstatement instead of DELETEstatement
1.使用SELECT语句而不是DELETE语句编写查询
SELECT COLUMNS
FROM Table1
INNER JOIN Table2 ON Table1.YYY = Table2.XXX
2.Replace SELECTCOLUMNS with DELETE FROM TABLE
2. 将SELECTCOLUMNS替换为DELETE FROM TABLE
DELETE FROM Table1
FROM Table1
INNER JOIN Table2 ON Table1.YYY = Table2.XXX
Note that we need to specify FROMtwice, one for DELETEpart and one for JOINpart.
请注意，我们需要指定FROM两次，一次用于DELETE部分，一次用于JOIN部分。
回答by SilverSkin
delete from TableA
where id in
(
select id from TableA
except select id from TableB
)
Which means "delete from tableA where id in table a but not in table b)
这意味着“从 tableA 中删除，其中 id 在表 a 但不在表 b 中）
Otherwise a Merge statement might help you (when matched/not matched delete etc) http://technet.microsoft.com/en-us/library/bb510625.aspx
否则，合并语句可能会帮助您（匹配/不匹配时删除等） http://technet.microsoft.com/en-us/library/bb510625.aspx
