Question:
In all the Foxpro 5.0 documentation, I can’t find an
example on how to update the contents of a field of
a table based on another table.
For example, table CUSTOMER has a column called STATE,
with state abbreviations. Another table called STATE
has the columns CODE, with the abbreviation of each state,
and STATENAME, with the state name spelled out. I want to change
the contents of the
CUSTOMER.STATE column, linking that column to the CODE field of STATE, to change the STATE field to a full name.
It’s a rather standard SQL update operation based on a linking column. What’s the correct syntax to do this?
Answer:
If you create an index called “state” on the state table, the following command would work:
This will overwrite CUSTOMER.STATE with the associated STATE.STATENAME.Are you sure that you mean to overwrite CUSTOMER.STATE, or do you mean to say that you also have a CUSTOMER.STATENAME that you want updated without touching the value of CUSTOMER.STATE? If that is the case, then the SQL UPDATE should be the following:
UPDATE CUSTOMER; SET statename = LOOKUP(STATE.STATENAME,CUSTOMER.STATE,STATE.CODE,"state")VFP’s SQL UPDATE is not as robust as some backends (i.e., cannot have columns updated by subqueries). If the replacing got much more complicated, you would need to perform a SCAN..ENDSCAN and replace the values just as easily.
