删除另一个表中不存在ID的所有行 [英] Delete all rows which has no id existing in another table

问题描述

我要删除另一个表中没有现有外键的所有行 例如:

I want to delete all rows which has no existing foreign key in another table example:

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    |
+----+-------+

查询将删除表2上ID为20和40的行.

The query will delete rows with id# 20 and 40 on table2.

我需要这样做，以便可以与table1和table2建立关系.

I need to do this so that i could establish a relationship with table1 and table2.

推荐答案

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

这篇关于删除另一个表中不存在ID的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！