Deleting orphans from a table

Question

I am trying to clean up a table where there are quite a few orphaned items.

I am approaching this by checking to see if there is a relationship to another table by looking for null values.

   DELETE FROM table1 
LEFT JOIN table2 ON table1.ID = table2.ID
    WHERE table2.ID IS NULL

I get an error that the left outer join is not valid.

I am looking for suggestions on other ways that I can delete these orphans from this broken relationship

Solution 1

try this:

DELETE  FROM        table1
WHERE NOT EXISTS (SELECT NULL FROM table2 WHERE table1.ID = table2.ID)

Solution 2

If you want to use the same syntax, here is how it could have been:

DELETE a 
FROM table1 a  
LEFT JOIN table2 b 
ON a.id = b.id 
WHERE b.id IS NULL 

Solution 3

Table 1 should then be the Child Table containing the orphaned records. And Table 2 the parent table.

    DELETE ChildTable  
    FROM Table1 ChildTable    
    LEFT JOIN Table2 ParentTable 
    ON ChildTable.id = ParentTable.id 
    WHERE ParentTable.id IS NULL 

A really helpful article. SQL JOINs make it easy to find and fix missing data

Comments

[Chris Smith]

I don't understand why but this runs MUCH faster than @MaziarTaheriAbkenar solution. The query plans in SQL Server are slightly different between yours and his but your runs so much faster in my world. +1

[t-clausen.dk]

@Smitty odd, I would have thought it was the same

[Teson]

This is the way to do it. A subquery is the wrong tool for this job.

[Q-life]

Response from mysql workbench : "You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect." I don't want to disable safe mode, that sounds so ... not safe.