SQL 如何删除 Teradata 表中不在另一个表中的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4321432/
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
How to delete rows in a Teradata table that are not in another table?
提问by Paul Hooper
What makes my situation tricky is that I don't have a single column key, with a simple list of primary keys to delete (for instance, "delete from table where key in ([list])"). I have multiple columns together as the primary key, and would need to join on all of them.
使我的情况变得棘手的是我没有一个列键，只有一个简单的主键列表要删除（例如，“从表中删除键（[列表]）”）。我有多个列作为主键，并且需要加入所有列。
Using what I know of other databases, I thought this might be done as:
使用我对其他数据库的了解，我认为这可能是这样做的：
DELETE FROM
table1 t1
LEFT OUTER JOIN
table2 t2
ON
t2.key1 = t1.key1 AND
t2.key2 = t1.key2
WHERE
t2.key1 IS NULL;
But Teradata (v12) responds with error number 3706, saying "Syntax error: Joined Tables are not allowed in FROM clause."
但是 Teradata (v12) 以错误号 3706 响应，说“语法错误：FROM 子句中不允许连接表。”
回答by Paul Hooper
Found this is done by:
发现这是通过以下方式完成的：
DELETE FROM
table1
WHERE
(key1, key2) NOT IN (
SELECT UNIQUE key1, key2 FROM table2
);
回答by Thomas
Another way is to use a correlated subquery:
另一种方法是使用相关子查询：
Delete From Table1
Where Not Exists(
Select 1
From Table2
Where Table2.key1 = Table1.key1
And Table2.key2 = Table1.key2
)
