Visual FoxPro Update does not update anything

Question

I know very little about VFP but I've been tasked with making some data alterations to a fairly large table. When running standard SQL update commands in the command window, VFP claims records are updating but they never change.

I tried this:

UPDATE selections ;
SET selections.gender = "FEMALE" ;
WHERE selections.gender = "F"

And VFP reported it had updated 4221674 records (the expected number) in about ten seconds. But when I re-queried the table, the gender field had not changed.

Just in case this was some anomaly based on the fact I was attempting to update the field I was filtering on, I tried this:

UPDATE selections ;
SET selections.gender = "FEMALE" ;
WHERE Urn = "946000001484066"

Which reported as having updated 1 record but changed nothing.

What am I doing wrong? Is there some sort of post-command process I need to invoke, like there is when you mark records for deletion and then have the actual delete step?

EDIT: This ...

REPLACE ALL gender WITH "FEMALE" for gender = "F"

Has the same effect - it says it's replaced 4 million records, but the data doesn't seem to change.

I have discovered that I cannot seem to add fields to the table either, which suggests some kind of lock. But I can't see any related setting.

DRapp DRapp · Accepted Answer · 2014-08-13T12:16:39

It may be that the table structure is only one character for the gender as one can only be either biologically Male or Female and the short reference of "M" or "F" respectively.

VFP is just not nagging you that you are trying to write a larger string value than the column in the table can handle and thus only keeping the "F" out of "FEMALE" attempt.

If you want the output in some report to show the full gender reference, you could do something like this in your sql query to get the data.

select ;
      iif( s.gender = "F", "FEMALE", "MALE" ) as ShowThisGender,;
      s.OtherColumns ;
   from ;
      Selections s;
   where ;
      conditions...

If you are within VFP, to confirm the structure, at the command window type

use Selections  
display structure

for clarification, you could use explicit path reference as to where your selections table is, such as

use X:\SomePath\SubPath\Selections

Visual FoxPro Update does not update anything

1 Answers