删除所有在另一张表中不存在的行。

16 浏览
0 Comments

删除所有在另一张表中不存在的行。

我想删除在另一张表中没有现有外键的所有行。

例子：

table1
+----+-------+
|id  | data  |
+----+-------+
| 1  | hi    |
+----+-------+
| 2  | hi    |
+----+-------+
| 3  | hi    |
+----+-------+
| 4  | hi    |
+----+-------+
| 5  | hi    |
+----+-------+
table2
+----+-------+
|a_id| data  |
+----+-------+
| 1  | hi    |
+----+-------+
| 20 | hi    |
+----+-------+
| 3  | hi    |
+----+-------+
| 40 | hi    |
+----+-------+
| 5  | hi    |
+----+-------+

这个查询将删除table2中id为20和40的行。

我需要这样做是为了建立table1和table2之间的关系。

0

2 答案

0 Comments

删除所有在另一个表中不存在的ID的行

问题原因：

在某些情况下，我们需要从一个表中删除那些在另一个表中不存在的ID的行。这可能是因为数据不一致，或者因为我们只对在另一个表中存在的数据感兴趣。然而，直接使用DELETE语句删除这些行可能会导致错误，因为它们可能会引用其他表中的数据。

解决方法：

为了解决这个问题，我们可以使用以下三种方法之一来删除这些行：

1. 使用NOT IN子查询：

   DELETE FROM Tab2 WHERE ID NOT IN (SELECT ID FROM Tab1)

这种方法使用了子查询来查找在Tab1表中存在的ID，然后将这些ID与Tab2表中的ID进行比较，并删除在Tab2表中不存在的ID的行。

2. 使用LEFT JOIN：

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

这种方法使用了LEFT JOIN来将table2表与table1表进行连接，并根据连接条件找到table1中不存在的ID。然后，通过WHERE子句将table1中不存在的ID的行从table2中删除。

3. 使用NOT EXISTS子查询：

   DELETE FROM table2
   WHERE NOT EXISTS (
     SELECT 1
     FROM table1
     WHERE table1.id = table2.a_id
   )

这种方法使用了NOT EXISTS子查询来检查table1中是否存在与table2中的a_id匹配的ID。如果不存在匹配的ID，则删除table2中的行。

根据《What's the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?》中的解释，不同的关系型数据库管理系统（RDBMS）对这些方法的执行效果可能有所不同。因此，我们应该根据具体的RDBMS选择最适合的方法来解决这个问题。

0
0 Comments

问题的出现原因：

在上述代码中，出现了一个问题：删除了table2中那些在table1中没有对应id的行。这可能是因为在实际应用中，我们需要确保table2中的数据与table1中的数据是对应的，不允许有任何没有对应id的行存在。

解决方法：

要解决这个问题，我们可以使用上述代码中的方法。首先，使用LEFT JOIN将table2和table1按照a_id和id进行连接。然后，使用WHERE子句来筛选出table1中没有对应id的行。最后，使用DELETE语句将筛选出的行从table2中删除。

代码如下：

DELETE table2

FROM table2

LEFT JOIN table1

ON table2.a_id = table1.id

WHERE table1.id IS NULL

通过执行上述代码，我们可以删除table2中那些没有对应id的行，确保table2中的数据与table1中的数据是完全对应的。这样可以避免在实际应用中出现数据不一致的情况。

0