SQL Delete Mimicking Left Join
December 1, 2009
It may seem a little silly, but sometimes I wish I could do something like a JOIN when I’m using DELETE. I came up with a workaround that does the job nicely (but maybe not efficiently, but hey, I don’t need to do this too often anyway).
DELETE FROM first_table WHERE ft_id IN (
SELECT ft_id FROM first_table f
LEFT JOIN second_table s ON s.ft_id = f.ft_id
WHERE s.ft_id IS NOT NULL
AND s.ft_id NOT IN (
SELECT third_table.id
FROM third_table
WHERE id > 100))
I like it because just by using a IN using a primary key you can select any particular entry you wanted to delete. And it’s a whole lot safer because you can test by just using the SELECT by itself.
