Microsoft KB Archive/126272

= How to Delete Records Having Identical ID Nums from Two Tables =

Article ID: 126272

Article Last Modified on 12/1/2003

-

APPLIES TO


 * 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

-



This article was previously published under Q126272



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

Additional query words: VFoxWin FoxWin FoxDos FoxMac

Keywords: kbcode KB126272

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.