Search code examples
mysqljoinsql-delete

Delete orphan records using Join

I got 2 tables as below. Ideally Table2.C1 is a foreign key of Table1.C1 however that foreign key was not setup initially. We are not able to setup the foreign key now because there are some orphan records exists in Table2 which we want to delete it before applying the alter statement.

Note: I do not want use any sub/nested query

Table1
--------
| C1   |
--------
| 1    |
| 2    |
| 3    |
| 4    |
|------|

Table2
--------
| C1   |
--------
| 1    |
| 1    |
| 8    |
| 9    |
| 2    |
|------|

I want to delete the records 8 and 9 from Table 2.

Solution 
  • DELETE table2.*
FROM
table2
LEFT OUTER JOIN table1 ON table2.c1 = table1.c1
WHERE
table1.c1 IS NULL