Re: Deleting orphaned records to establish Ref Integrity

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> "Roman F" <romanf(at)fusemail(dot)com> writes:
>
> > DELETE FROM child_table WHERE parentid NOT IN
> > (SELECT parentid FROM parent_table)
>
> Another idea is to try an outer join:
>
> SELECT child_table.parentid INTO tmp_table
> FROM child_table LEFT JOIN parent_table
> ON (child_table.parentid = parent_table.parentid)
> WHERE parent_table.parentid IS NULL;

There's also

DELETE
FROM child_table
WHERE NOT EXISTS (select 1
from parent_table
where parent_id = child_table.parent_id
)

Which won't use anything as efficient as a hash join or merge join but will be
at least capable of using index lookups for something basically equivalent to
a nested loop.

--
greg