How to Delete Orphaned Rows in MySQL Based on Mismatched IDs?

Delete MySQL rows based on ID mismatch

To delete entries in a MySQL table that are missing corresponding IDs in another table, follow this method:

Use LEFT JOIN/IS NULL

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

This query uses LEFT JOIN to match rows between the "BLOB" and "FILES" tables. It deletes rows in "BLOB" whose corresponding "id" column in "FILES" is NULL, indicating the existence of orphan entries.

USE NOT EXISTS

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

This query uses a subquery to check if each row of "BLOB" has a matching row in "FILES". If there is no match, the outer query will remove the row from "BLOB".

Use NOT IN

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

This query uses the NOT IN operator to identify those rows in the "BLOB" table for which a "fileid" value does not exist in the "FILES" table, effectively removing orphan entries.

Attention

When performing DELETE operations, consider using transactions if your database engine supports it (e.g., not MyISAM). This allows you to roll back changes if an error occurs.

The above is the detailed content of How to Delete Orphaned Rows in MySQL Based on Mismatched IDs?. For more information, please follow other related articles on the PHP Chinese website!