Delete all rows which has no id existing in another table
database-relations, mysql, sql
Solution
DELETE table2
FROM table2
LEFT JOIN table1
ON table2.a_id = table1.id
WHERE table1.id IS NULL
Problem
I want to delete all rows which has no existing foreign key in another table example: ``` table1 +----+-------+ |id | data | +----+-------+ | 1 | hi | +----+-------+ | 2 | hi | +----+-------+ | 3 | hi | +----+-------+ | 4 | hi | +----+-------+ | 5 | hi | +----+-------+ table2 +----+-------+ |a_id| data | +----+-------+ | 1 | hi | +----+-------+ | 20 | hi | +----+-------+ | 3 | hi | +----+-------+ | 40 | hi | +----+-------+ | 5 | hi | +----+-------+ ``` The query will delete rows with id# 20 and 40 on table2. I need to do this so that i could establish a relationship with table1 and table2.