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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings