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
Roman F [EMAIL PROTECTED] writes:
... Executing something like
the following statement would work, but even with indexes it takes an
insane amount of time to execute for each of the tables:
DELETE FROM child_table WHERE parentid NOT IN
(SELECT parentid FROM parent_table)
Uh, what sort of
Tom Lane [EMAIL PROTECTED] writes:
Roman F [EMAIL PROTECTED] 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
I have several large tables (10 million to 200 million rows) that have
foreign keys with each other by *convention*, but no actual FOREIGN KEY
constraints.
Over the course of years, orphaned records (children with no parent) have
accumulated and now I want to clean them up. I can't just create