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 sameidentification number and exist in two separate tables.
You can use this method to delete duplicates that exist in two separate butsimilarly defined tables. Or you can use it, for example, to delete acustomer from your system when that customer has records in a master tableand transaction tables. For example, you might want to create a temporarytable filled with customer numbers to be deleted. Then use one of themethods in this article to mark the duplicates for deletion from the masterand transaction tables.
The following two methods mark duplicate records in CUSTOMER.DBF fordeletion, so make sure you have a backup copy of CUSTOMER.DBF.
SCAN...ENDSCAN loop method.
SET RELATION command method.
To demonstrate these techniques, you need to create a table containingduplicate records. Issue the following commands to create apractice table and index file from the CUSTOMER.DBF table:
IF _MAC=.T. SET DEFAULT TO "Hard drive:FoxPro 2.6:Tutorial:" ELSE SET DEFAULT TO Sys(2004)+"Tutorial" && SET DEFAULT TO SYS(2004)+"Samples\Data" in Visual FoxPro ENDIF USE CUSTOMER.DBF COPY TO TEST.DBF FOR RECNO() < 10 USE TEST INDEX ON cno TAG cno ADDITIVE && INDEX on cust_id TAG custid ADDITIVE in Visual FoxPro
The TEST.DBF table now contains records from the CUSTOMER.DBF table. Theserecords 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 Records
The following program searches the TEST.DBF file and marks the duplicaterecords in CUSTOMER.DBF for deletion:
USE Customer IN 0 USE Test IN 0 ORDER TAG CNO SELECT Customer SCAN m.cno=cno && m.custid=cust_id in Visual FoxPro SELECT Test SEEK(m.cno) && SEEK (m.custid) in Visual FoxPro IF FOUND()= .T. SELECT Customer DELETE ENDIF SELECT Customer ENDSCAN
Method Two: SET RELATION and FOUND() Function Method
This method sets up a one-to-one relationship between the two tables. Afterestablishing the relationship, the DELETE command moves through theCustomer table comparing records with those in the Test table. If theFOUND() function returns the logical value true, DELETE marks the matchingrecord in CUSTOMER.DBF. After executing this code, the first nine recordsare deleted in the Customer table.
USE Customer.dbf in 0 USE Test.dbf IN 0 ORDER TAG cno SELECT Customer SET RELATION TO cno INTO Test ADDITIVE DELETE ALL FOR FOUND('Test')