如何删除 Teradata 表中不在另一个表中的行？

发布于 2024-10-05 12:31:32 字数 350 浏览 8 评论 0原文

让我的情况变得棘手的是，我没有单个列键，只有一个要删除的简单主键列表（例如，“从表中删除（[列表]）”）。我有多个列作为主键，并且需要加入所有列。

根据我对其他数据库的了解，我认为这可能会这样做：

DELETE FROM
    table1 t1
  LEFT OUTER JOIN
      table2 t2
    ON
      t2.key1 = t1.key1 AND
      t2.key2 = t1.key2
  WHERE
    t2.key1 IS NULL;

但 Teradata (v12) 响应错误号 3706，指出“语法错误：FROM 子句中不允许使用连接表”。

原文

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."

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

烟若柳尘 2024-10-12 12:31:32

发现这是通过以下方式完成的：

DELETE FROM
    table1
  WHERE
    (key1, key2) NOT IN (
      SELECT UNIQUE key1, key2 FROM table2
    );

Found this is done by:

DELETE FROM
    table1
  WHERE
    (key1, key2) NOT IN (
      SELECT UNIQUE key1, key2 FROM table2
    );

回复收藏 0 原文

星 2024-10-12 12:31:32

另一种方法是使用相关子查询：

Delete From Table1
Where Not Exists(
                Select 1 
                From Table2 
                Where Table2.key1 = Table1.key1
                    And Table2.key2 = Table1.key2
                )

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
                )

回复收藏 0 原文

~没有更多了~