Re: Deleting orphaned records to establish Ref Integrity

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 beat

least capable of using index lookups for something basically equivalent toa
nested loop.

Sorry for the delay, I only get to work on this system every now and then.

I tried Greg's suggestion and it worked out great. The estimates from
EXPLAIN were much larger than the actuals (e.g. 41 hours vs. 2 hours), so
I probably have some tuning to do with this dataset. I decided not to try
Tom's temp table method because I was afraid the generated table would be
very large, so the subsequent DELETE .. WHERE NOT IN (...) would cause
swapping again.

Thanks all for your help, your insights saved me a lot of headache.

Roman

_____________________________________
Consolidate your email!
http://www.fusemail.com

_____________________________________
Check All Email Accounts Anywhere!
Check your POP3 and webmail account
from any PC. With no ads
http://www.fusemail.com