Re: Deleting orphaned records to establish Ref Integrity - Mailing list pgsql-general

Tom Lane <tgl@sss.pgh.pa.us> writes:

> "Roman F" <romanf@fusemail.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