Article ID: 126272 - View products that this article applies to.
This article was previously published under Q126272
This article has been archived. It is offered "as is" and will no longer be updated.
This article shows by example how to delete records that share the same identification number and exist in two separate tables.
You can use this method to delete duplicates that exist in two separate but similarly defined tables. Or you can use it, for example, to delete a customer from your system when that customer has records in a master table and transaction tables. For example, you might want to create a temporary table filled with customer numbers to be deleted. Then use one of the methods in this article to mark the duplicates for deletion from the master and transaction tables.
The following two methods mark duplicate records in CUSTOMER.DBF for deletion, so make sure you have a backup copy of CUSTOMER.DBF.
The TEST.DBF table now contains records from the CUSTOMER.DBF table. These records serve as the duplicate records for the examples listed below. A .CDX index also exists for the TEST.DBF.
Method One: SCAN...ENDSCAN Loop Routine to Find Duplicate RecordsThe following program searches the TEST.DBF file and marks the duplicate records in CUSTOMER.DBF for deletion:
Method Two: SET RELATION and FOUND() Function MethodThis method sets up a one-to-one relationship between the two tables. After establishing the relationship, the DELETE command moves through the Customer table comparing records with those in the Test table. If the FOUND() function returns the logical value true, DELETE marks the matching record in CUSTOMER.DBF. After executing this code, the first nine records are deleted in the Customer table.
Article ID: 126272 - Last Review: February 28, 2014 - Revision: 2.1