updating a table in VFP

0
votes

There are two tables in my database. I am trying to update a column in table2 by setting it equal to one of the columns in table1. I've already looked at this answer visual foxpro - need to update table from another table And tried to do this on my code, however, I kept having a syntax error on UPDATE table2. Why? Here is what I have.

ALTER TABLE table2;
ADD COLUMN base2 B(8,2);
UPDATE table2
WHERE table2.itemid=table1.itemid from table1;
SET table2.base2=table1.base;
3
visual-foxprofoxpro
You should only tag the question with the actual database you're using. If you're using Visual Foxpro, you don't need the final ; in the update command. - Herb
Ok! I will erase the sql language. I deleted the final ; and still am getting the syntax error - M-M
When I erased all ";" It seems like my problem is the add column clause - M-M
Actually it appears you're using the ; wrong. You only need one at the end of a line that's being continued. Try writing each command on one line, rather than breaking it into multiple lines. - Herb
I did that, and still got a syntax error - M-M

3 Answers

1
votes

The simplest syntax is:

update table2 from table1 where table2.itemid = table1.itemid ;
set table2.base2 = table1.base

You could also add more fields to update separated by commas, i.e.

... set table2.base2 = table1.base, table2.this = table1.that
0
votes

Use join

          Update table2 b 
         Join table1 a on b. Itemid=a.itemid
          Set b. Base2=a.base
0
votes

Using 'standard' VFP language syntax and RELATED Tables, you could quite easily do the following: 

USE Table1 IN 0 EXCLUSIVE
SELECT Table1
INDEX ON ID TAG ID  && Create Index on ID field
USE Table2 IN 0
SELECT Table2
SET RELATION TO ID INTO Table1
REPLACE ALL Table2.ID WITH Table1.ID FOR !EMPTY(Table2.ID)

You might want to spend some time looking over the free, on-line tutorial videos at: Learn Visual Foxpro @ garfieldhudson.com
The videos named:
* Building a Simple Application - Pt. 5
and
* Q&A: Using Related Tables In A Report
Both discuss using VFP's language to work with Related Tables

Good Luck