Search code examples
sqlsql-servert-sqlsql-delete

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

Solution

  • try this:

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