CODEWITHSUNDEEP
visual-foxprofoxpro
M-M
M-M

Reputation: 901

updating a table in VFP

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;

Upvotes: 0

Views: 5754

Answers (3)

Alan B
Alan B

Reputation: 4288

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

Upvotes: 1

Dhugalmac
Dhugalmac

Reputation: 574

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

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

Use join

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

Upvotes: 0

Related Questions