Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Visual FoxPro
Expertise: Beginner
Jul 16, 1999

SQL UPDATE 2 TABLES

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:


UPDATE  CUSTOMER;
   SET state = LOOKUP(STATE.STATENAME,CUSTOMER.STATE,STATE.CODE,"state")
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.
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date