从表中删除孤儿

发布于 2024-11-18 18:35:57 字数 255 浏览 2 评论 0原文

我正在尝试清理一张有很多孤立项目的桌子。

我通过查找空值来检查是否与另一个表存在关系来解决这个问题。

   DELETE FROM table1 
LEFT JOIN table2 ON table1.ID = table2.ID
    WHERE table2.ID IS NULL

我收到一条错误消息，指出左外连接无效。

我正在寻找其他方法的建议，以便我可以将这些孤儿从这段破裂的关系中删除

原文

I am trying to clean up a table where there are quite a few orphaned items.

I am approaching this by checking to see if there is a relationship to another table by looking for null values.

   DELETE FROM table1 
LEFT JOIN table2 ON table1.ID = table2.ID
    WHERE table2.ID IS NULL

I get an error that the left outer join is not valid.

I am looking for suggestions on other ways that I can delete these orphans from this broken relationship

分享到QQ

分享到微博

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

发布评论

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

_失温 2024-11-25 18:35:57

如果您想使用相同的语法，可以这样：

DELETE a 
FROM table1 a  
LEFT JOIN table2 b 
ON a.id = b.id 
WHERE b.id IS NULL

If you want to use the same syntax, here is how it could have been:

DELETE a 
FROM table1 a  
LEFT JOIN table2 b 
ON a.id = b.id 
WHERE b.id IS NULL

回复收藏 0 原文

笑看君怀她人 2024-11-25 18:35:57

试试这个：

DELETE  FROM        table1
WHERE NOT EXISTS (SELECT NULL FROM table2 WHERE table1.ID = table2.ID)

try this:

DELETE  FROM        table1
WHERE NOT EXISTS (SELECT NULL FROM table2 WHERE table1.ID = table2.ID)

回复收藏 0 原文

请止步禁区 2024-11-25 18:35:57

表 1 应该是包含孤立记录的子表。表2是父表。

    DELETE ChildTable  
    FROM Table1 ChildTable    
    LEFT JOIN Table2 ParentTable 
    ON ChildTable.id = ParentTable.id 
    WHERE ParentTable.id IS NULL

一篇非常有帮助的文章。
SQL JOIN很容易找到并修复丢失的数据

Table 1 should then be the Child Table containing the orphaned records. And Table 2 the parent table.

    DELETE ChildTable  
    FROM Table1 ChildTable    
    LEFT JOIN Table2 ParentTable 
    ON ChildTable.id = ParentTable.id 
    WHERE ParentTable.id IS NULL

A really helpful article.
SQL JOINs make it easy to find and fix missing data

回复收藏 0 原文

~没有更多了~