How to Delete Records Having Identical ID Nums from Two Tables (126272)

SUMMARY

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.

MORE INFORMATION

The following two methods mark duplicate records in CUSTOMER.DBF for deletion, 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 containing duplicate records. Issue the following commands to create a practice 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. 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 Records

The following program searches the TEST.DBF file and marks the duplicate records 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. 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.

   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')