MySQL 从另一个表中删除 ID 不匹配的 sql 行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3384127/
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
Delete sql rows where IDs do not have a match from another table
提问by Martin
I'm trying to delete orphan entries in a mysql table.
我正在尝试删除 mysql 表中的孤立条目。
I have 2 tables like this:
我有 2 张这样的表：
Table files:
表files：
| id | ....
------------
| 1 | ....
| 2 | ....
| 7 | ....
| 9 | ....
table blob:
表blob：
| fileid | ....
------------
| 1 | ....
| 2 | ....
| 3 | ....
| 4 | ....
| 4 | ....
| 4 | ....
| 9 | ....
The fileidand idcolumns can be used to join the tables together.
该fileid和id列可以被用来连接表在一起。
I want to delete all rows in table blobwhere fileidcannot be found in the table files.id.
我想删除表blob中fileid找不到的所有行files.id。
So using the example above that would delete rows: 3 & 4(s) in the blobtable.
因此，使用上面的示例将删除表中的行：3 & 4(s) blob。
回答by OMG Ponies
Using LEFT JOIN/IS NULL:
使用 LEFT JOIN/IS NULL：
DELETE b FROM BLOB b
LEFT JOIN FILES f ON f.id = b.fileid
WHERE f.id IS NULL
Using NOT EXISTS:
使用不存在：
DELETE FROM BLOB
WHERE NOT EXISTS(SELECT NULL
FROM FILES f
WHERE f.id = fileid)
Using NOT IN:
使用不在：
DELETE FROM BLOB
WHERE fileid NOT IN (SELECT f.id
FROM FILES f)
Warning
警告
Whenever possible, perform DELETEs within a transaction (assuming supported - IE: Not on MyISAM) so you can use rollback to revert changes in case of problems.
只要有可能，在事务中执行 DELETE（假设支持 - IE：不在 MyISAM 上），以便在出现问题时可以使用回滚来恢复更改。
回答by Martin Smith
DELETE FROM blob
WHERE fileid NOT IN
(SELECT id
FROM files
WHERE id is NOT NULL/*This line is unlikely to be needed
but using NOT IN...*/
)
回答by George
DELETE FROM blob
WHERE NOT EXISTS (
SELECT *
FROM files
WHERE id=blob.id
)
回答by Kamrujjaman Khan
delete from table1 t1
WHERE not exists (select id from table2 where related_field_in_t2=t1.id)
AND not exists (select id from table3 where related_field_in_t3=t1.id)
AND not exists (select id from table4 where related_field_t4=t1.id)
AND not exists (select id from table5 where related_field_t5=t1.id);
