SQL 删除...从...在哪里...在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8019957/
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 ... FROM ... WHERE ... IN
提问by WoltjerD
i'm looking for a way to delete records in table 1 with matching combinations in table 2 on 'stn' and 'jaar'. The contents of column 'jaar' in table2 is formatted in a previous stage/query by
我正在寻找一种方法来删除表 1 中的记录，以及表 2 中“stn”和“jaar”的匹配组合。table2 中列 'jaar' 的内容在前一阶段/查询中被格式化
year(datum) AS 'jaar'
年份（基准）AS 'jaar'
Sorry, can't find again the site where i found this "solution".
抱歉，无法再次找到我找到此“解决方案”的站点。
DELETE FROM table1
WHERE stn, year(datum) IN (SELECT stn, jaar FROM table2);
回答by DavidEG
You can achieve this using exists:
您可以使用exists以下方法实现此目的：
DELETE
FROM table1
WHERE exists(
SELECT 1
FROM table2
WHERE table2.stn = table1.stn
and table2.jaar = year(table1.datum)
)
回答by Sklivvz
The canonical T-SQL (SqlServer) answer is to use a DELETEwith JOINas such
规范T-SQL（SqlServer的）的答案是使用一个DELETE与JOIN这样
DELETE o
FROM Orders o
INNER JOIN Customers c
ON o.CustomerId = c.CustomerId
WHERE c.FirstName = 'sklivvz'
This will delete all orders which have a customer with first name Sklivvz.
这将删除所有客户名为 Sklivvz 的订单。
回答by onedaywhen
Try adding parentheses around the row in table1e.g.
尝试在行周围添加括号，table1例如
DELETE
FROM table1
WHERE (stn, year(datum)) IN (SELECT stn, jaar FROM table2);
The above is Standard SQL-92 code. If that doesn't work, it could be that your SQL product of choice doesn't support it.
以上是标准 SQL-92 代码。如果这不起作用，则可能是您选择的 SQL 产品不支持它。
Here's another Standard SQL approach that is more widely implemented among vendors e.g. tested on SQL Server 2008:
这是在供应商中更广泛实施的另一种标准 SQL 方法，例如在 SQL Server 2008 上进行测试：
MERGE INTO table1 AS t1
USING table2 AS s1
ON t1.stn = s1.stn
AND s1.jaar = YEAR(t1.datum)
WHEN MATCHED THEN DELETE;
