Delete orphaned records

[trick66]

I have a number of tables that all belong to a single database. One of the tables, ROUTNO, is considered the "header" table which has ROUT_NO as an index. The other tables also have ROUT_NO as an index.

What I want to accomplish is to create a command that will delete records from the "minor" tables that has ROUT_NO that DOES NOT exist any longer in the header table. Is this possible and how?

Thanks for any advice anyone can give.

Rick

 

[Mike Lewis]

Rick,

Off the top of my head, something like this:

DELETE FROM MinorTable WHERE Route_No NOT IN;
  (SELECT Route_No FROM Header_Table)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[mspratt]

Hi Rick,

Be easier if you had specified VFP Version etc, and whether the index is .idx or .cdx. Probably .idx, but if is .ndx the children must all be in different directories. .cdx (structural) indexes have the same name as the table (.dbf).

Regards,

Mike

 

[trick66]

Sorry for the lack of information. It is VFP 7. But what Mike Lewis gave me worked. You guys are great, thanks again.

Rick