APPEND / REPLACE records in a .dbf table (ez?)

[kev100]

I've got a VFP app that gets its data from a .dbf file.

Well...some of the records in that file (about 20%) have 1 field that is wrong.

I've got an updated .dbf that is just like the original, except with the correct data in the fields of those records.


For a few odd reasons...I cannot simply delete the old .dbf and put this new one in it's place.

I'm not that familar with the line-by-line Foxpro commands that can be entered when you open up a .dbf file and "Browse" it's contents....but...

....from that place I understand that various commands like "Pack," etc...can be executed there.

I'm hoping there is a command like "Update" or something...that will look at the "target" .dbf....look at the "new" .dbf....and simply update whatever matching records exist between the 2 in the Existing Target with any data that is different in the "new" .dbf.

The end result hoped for is to be able to continue using the current dbf...but just edited with the correct data from the new (correct) one.

Hope this makes sense...
Any advice appreciated.

THANKS

 

[TamarGranor]

Assuming there's a field that uniquely identifies each record (I'll call it ID), this isn't hard.

Let me say up front that I haven't tested this code, so copy everything before you start.

In VFP 9, you can do it like this:

UPDATE TheRealTable ;
   SET TheBadField = TheCopyTable.TheBadField ;
   FROM TheCopyTable ;
   WHERE TheRealTable.ID = TheCopyTable.ID

In VFP 8 and earlier, you'll have to set a relation to do this:

USE TheCopyTable ORDER ID IN 0
SELECT 0
USE TheRealTable
SET RELATION TO ID INTO TheCopyTable
REPLACE ALL TheBadField WITH TheCopyTable.TheBadField IN TheRealTable

Hope this helps.

Tamar

 

[kev100]

Thanks very much...I will give this a try.

Will be using VFP 7.0

To be sure I'm grasping this.....If the Current table with the bad data (the one I want to keep) is called:

DBTABLE_Flawed

And the new table (which is identical except with correct data) is called:

DBTABLE_Fix


The unique key field in each is AdvID

The field that contains the incorrect data is TEL

...then the command would be...

USE TheCopyTable ORDER ID IN 0
SELECT 0
USE DBTable_Flawed
SET RELATION TO ID INTO DBTable_Fix
REPLACE ALL TEL WITH DBTableFIX.TEL IN DBTable_Flawed

(?)


Thanks very much...this could save quite a hassle.

 

[kev100]

The first few commands seem to to okay...however, the command

SET RELATION TO ID INTO TheCopyTable

Gets the error:
"Command contains uncrecognized phrase/keyword"

(am using VFP 7.0)

Thanks

 

[kev100]

I also tried the VFP 9

UPDATE TheRealTable ;
SET TheBadField = TheCopyTable.TheBadField ;
FROM TheCopyTable ;
WHERE TheRealTable.ID = TheCopyTable.ID

...and the first 3 lines seemed to execute.

However, the last line get the same error as above...

It may have something to do with syntax b/c I noticed that identifing the table required c:\recfolder\TheCopyTable.dbf.ID

(something about TheCopyTable.dbf.ID just looks awkward)

But...the last line of either attempt get the error message:
"Command contains uncrecognized phrase/keyword"

 

[TamarGranor]

Looks like you're not completely substituting your table and field names into my code. Try this:

USE DBTableFix ORDER AdvID IN 0
SELECT 0
USE DBTable_Flawed
SET RELATION TO AdvID INTO DBTable_Fix
REPLACE ALL TEL WITH DBTableFIX.TEL IN DBTable_Flawed

Tamar