How to Delete Records Having Identical ID Nums from Two Tables

This article was previously published under Q126272
This article has been archived. It is offered "as is" and will no longer be updated.
SUMMARY
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.
MORE INFORMATION
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')				
VFoxWin FoxWin FoxDos FoxMac
Properties

Article ID: 126272 - Last Review: 02/28/2014 07:56:12 - Revision: 2.1

  • Microsoft Visual FoxPro 3.0 Standard Edition
  • Microsoft FoxPro 2.5b
  • Microsoft FoxPro 2.5a
  • Microsoft FoxPro 2.5b
  • Microsoft FoxPro 2.6 Standard Edition
  • Microsoft FoxPro 2.6a Standard Edition
  • Microsoft FoxPro 2.5b for MS-DOS
  • Microsoft FoxPro 2.5a
  • Microsoft FoxPro 2.5b for MS-DOS
  • Microsoft FoxPro 2.6 for MS-DOS
  • Microsoft FoxPro 2.6a Standard Edition
  • Microsoft FoxPro 2.5b for Macintosh
  • Microsoft Visual FoxPro 2.5c for Macintosh
  • Microsoft FoxPro 2.6a Professional Edition for Macintosh
  • kbnosurvey kbarchive kbcode KB126272
Feedback