Tom Lane <tgl@xxxxxxxxxxxxx> writes:
> "Roman F" <romanf@xxxxxxxxxxxx> 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
