Delete sql rows where IDs do not have a match from another table
SqlMysqlSql Problem Overview
I'm trying to delete orphan entries in a mysql table.
I have 2 tables like this:
Table files
:
| id | ....
------------
| 1 | ....
| 2 | ....
| 7 | ....
| 9 | ....
table blob
:
| fileid | ....
------------
| 1 | ....
| 2 | ....
| 3 | ....
| 4 | ....
| 4 | ....
| 4 | ....
| 9 | ....
The fileid
and id
columns can be used to join the tables together.
I want to delete all rows in table blob
where fileid
cannot be found in the table files.id
.
So using the example above that would delete rows: 3 & 4(s) in the blob
table.
Sql Solutions
Solution 1 - Sql
Using 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.
Solution 2 - Sql
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...*/
)
Solution 3 - Sql
DELETE FROM blob
WHERE NOT EXISTS (
SELECT *
FROM files
WHERE id=blob.id
)
Solution 4 - Sql
DELETE FROM <table>
WHERE <row column you want to delete by > not in
(select <column you want to compare other column> from <other table>)
This deletes the row when the first column doesn't appear on the second table