Delete sql rows where IDs do not have a match from another table

Question

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.

Answer 1

You can use LEFT JOIN/IS NULL like this:

DELETE b FROM BLOB b 

 LEFT JOIN FILES f ON f.id = b.fileid 

 WHERE f.id IS NULL

Use NOT EXISTS like this:

DELETE FROM BLOB 

WHERE NOT EXISTS(SELECT NULL

 FROM FILES f

 WHERE f.id = fileid)

Use NOT IN this way:

DELETE FROM BLOB

 WHERE fileid NOT IN (SELECT f.id 

 FROM FILES f)

Note: Whenever it's possible, perform DELETEs within the transaction (assuming supported - IE: Not on MyISAM) therefore, you can use rollback to revert changes in case of problems.