Appending Question on 2 databases

[Jarrett311]

I have 2 databases.

1. Mail.dbf
2. Master.dbf

Both databases have the same fields. The phone field on the mail.dbf is blank. I want to match the address and zip code from the master database with the mail database. When there is a match, I want to copy the phone number on that record (which is in the master.dbf) to the mail.dbf record. Will someone please help me write a simple program to do this?

Thanks,

Jarrett

 

[dgrewe]

CLOSE ALL
USE MASTER alias MASTER
SELECT 0
USE MAIL ALIAS MAIL Exclu
INDEX on ADDRESS+ZIPCODE TO TEMPMAIL.IDX (in case you do NOT have a index)
SET INDEX TO TEMPMAIL
SELECT MASTER
GOTO TOP
SCAN ALL
SELECT MAIL
SEEK MASTER.ADDRESS+MASTER.ZIPCODE
IF FOUND()
replace MAIL.PHONE WITH MASTER.PHONE
ENDIF
SELECT MASTER
ENDSCAN
CLOSE ALL
*
2.6 Has a Select Command, so maybe some one remembers it's limitations and can do it in a query for you.


David W. Grewe Dave

 

[TamarGranor]

In VFP, I'd do this with the SQL UPDATE command, but in 2.6, SET RELATION is the ticket.

SELECT 0
USE Master
* If you already have an index on Address+Zip, use the next line:
SET ORDER TO AddressZIP
* Otherwise, use the next line to create one:
INDEX ON UPPER(Address + ZIP) TAG AddressZIP

SELECT 0
USE Mail

SET RELATION TO UPPER(Address + ZIP) INTO Master

REPLACE Phone WITH Master.Phone FOR NOT EOF("Master")

Tamar

 

[cricket]

I would suggest using ALLTRIM or LTRIM to clean up leading spaces, but that has its own Indexing issues.