Deleting orphans from a table

问题:

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

回答1:

try this:

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

回答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 

回答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