删除id与其他表不匹配的sql行

我试图删除一个mysql表中的孤儿条目。

我有两张这样的表格:

表files:

| id | ....
------------
| 1  | ....
| 2  | ....
| 7  | ....
| 9  | ....

表blob:

| fileid | ....
------------
| 1  | ....
| 2  | ....
| 3  | ....
| 4  | ....
| 4  | ....
| 4  | ....
| 9  | ....

fileid和id列可用于将表连接在一起。

我想删除表blob中的所有行，其中fileid不能在表files.id中找到。

因此，使用上面的例子将删除行:3 &blob表中的4(s)。

209807 次浏览

小开

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...*/
)

小开

最佳答案

使用左连接/IS NULL:

DELETE b FROM BLOB b
LEFT JOIN FILES f ON f.id = b.fileid
WHERE f.id IS NULL

DELETE FROM BLOB
WHERE NOT EXISTS(SELECT NULL
FROM FILES f
WHERE f.id = fileid)

DELETE FROM BLOB
WHERE fileid NOT IN (SELECT f.id
FROM FILES f)

只要可能，在事务中执行delete(假设支持- IE: MyISAM上不支持)，以便在出现问题时使用回滚恢复更改。

小开

DELETE FROM blob
WHERE NOT EXISTS (
SELECT *
FROM files
WHERE id=blob.id
)

小开

DELETE FROM <table>
WHERE <row column you want to delete by > not in
(select <column you want to compare other column> from <other table>)

当第一列不会出现在第二个表

上