Recently I ran into an issue with an application I had developed where I made the mistake of not deleting some child table records when the parent record was deleted. After the app had been in beta for a month or two, I realized that I had thousands of records in the child tables that had no parents and were thus “orphan” records.
After juggling a few ideas in my head, I decided the best approach to this would be to make a temporary table of just the orphan items and then delete from the child table where the ID matches an ID in my orphans table. What does this look like in MySQL?
CREATE TEMPORARY TABLE Orphans AS ( SELECT ChildTable.id FROM ChildTable LEFT OUTER JOIN ParentTable ON ChildTable.RelationKey = ParentTable.RelationKey WHERE ParentTable.RelationKey IS NULL ); DELETE FROM ChildTable WHERE ChildTable.id IN (SELECT id FROM Orphans); DROP TABLE Orphans; |
How Does it Work?
Essentially, the first line of the query makes a new temporary table that we will delete when we are done:
CREATE TEMPORARY TABLE Orphans AS (
The second line selects the primary key (id) of every record in Child Table.
The third line creates a Left Outer Join with the parent table that used to have records associated with your orphaned records, based on whichever field you use to associate the two tables (RelationKey).
The fourth line specifies that we only want to select the ones where the RelationKey we provide is NULL, meaning it couldn’t find a parent element.
At this point we build the table and we now have a table of all orphaned records.
The last command just runs a DELETE query telling the database to delete any records that exist in both the ChildTable and the new Orphans table. Once that’s done, delete the Orphans table and the operation is done.