我试图删除一个mysql表中的孤儿条目。
我有两张这样的表格:
表文件:
| id | ....
------------
| 1 | ....
| 2 | ....
| 7 | ....
| 9 | ....
表团:
| fileid | ....
------------
| 1 | ....
| 2 | ....
| 3 | ....
| 4 | ....
| 4 | ....
| 4 | ....
| 9 | ....
fileid和id列可用于将表连接在一起。
我想删除表blob中fileid不能在表files.id中找到的所有行。
因此，使用上面的例子，将删除行:3和4(s)在blob表。
当前回答
使用左连接/IS NULL:
DELETE b FROM BLOB b
LEFT JOIN FILES f ON f.id = b.fileid
WHERE f.id IS NULL
使用NOT EXISTS:
DELETE FROM BLOB
WHERE NOT EXISTS(SELECT NULL
FROM FILES f
WHERE f.id = fileid)
使用NOT IN:
DELETE FROM BLOB
WHERE fileid NOT IN (SELECT f.id
FROM FILES f)
警告
只要可能，在事务中执行delete(假设支持- IE: MyISAM上不支持)，以便在出现问题时使用回滚恢复更改。
其他回答
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
使用NOT EXISTS:
DELETE FROM BLOB
WHERE NOT EXISTS(SELECT NULL
FROM FILES f
WHERE f.id = fileid)
使用NOT IN:
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>)
时删除该行 第一列没有出现在第二个表上
