Richard Huxton wrote:
> DELETE FROM table1 WHERE col1 NOT IN (SELECT id from table2);
Just as a side note: If you have a large number of missing IDs and don't
want to wait a long time, you may be better off with something like
(untested, but I think it's right - TEST FIRST):
SELECT *
FROM table1 t1 LEFT OUTER JOIN table2 t2 ON (t1.ref_col=t2.id)
WHERE t2.id IS NULL;
-- check that the rows to be deleted are OK
DELETE FROM table1
USING table1 t1 LEFT OUTER JOIN table2 t2 ON (t1.ref_col=t2.id)
WHERE table1.id = t1.id AND t2.id IS NULL;
(by the way, being able to specify an explicit join method in a DELETE
... USING or update ... USING would be *great*).
--
Craig Ringer