You can use SET RELATION TO commands
Let's assume that you already have FieldA1 index in Table1 as well as FieldA2 in Table2
USE table1 IN 0 ORDER fieldA1
USE table2 IN 0
SELECT table2
SET RELATION TO fieldA2 INTO table1 ADDITIVE
REPLACE ALL fieldC WITH fieldB
It is important that indexes should have been made on all tables before running program. It is not advisable to create index at run time...
>I have following FOXPRO (FoxPRO 5.0 service Pack 2) problem :
>
>I have two tables, containing different information. These tables may be
>connected by use of one column containing an integervalue.
>My problem is how I can 'copy' one column of this table towards the
>other table.
>
>EX.
> TableOne TableTwo
>
>fieldA1 fieldB fieldA2 fieldC
>100 45 100 0
>101 21 101 0
>102 44 102 0
>103 88 103 0
>104 70 104 0
>105 21 105 0
>
>should become :
>
> TableOne TableTwo
>
>fieldA1 fieldB fieldA2 fieldC
>100 45 100 45
>101 21 101 21
>102 44 102 44
>103 88 103 88
>104 70 104 70
>105 21 105 21
>
>
>
>Currently I do the following :
>
> && create temporary index on table two
>
> Select TableTwo
> index on field2A TAG theIndex
> SET ORDER TO TheIndex
>
> Select tableOne
> SCAN
> select TableTwo
> Seek TableOne.fieldA1
> IF FOUND()
> REPLACE TableOne.fieldA1 WITH TableTwo.fieldA2
>IN TableOne
> ENDIF
> Select TableOne
> ENDSCAN
>
>Of course, this works but far too slow at my opinion.
>Is there someone who knows how to deal with this problem in a more
>efficient way , for example by use of an SQL UPDATE like :
>
> UPDATE TableOne SET fieldB = (SELECT tableTwo.fieldC FROM
>tabelTwo WHERE TableTwo.fieldA2 = TableOne.fieldA1)
>
>JDG
JESS S. BANAGA
Project Leader - SDD division
...shifting from VFP to C#.Net
CHARISMA simply means: "Be more concerned about making others feel good about themselves than you are in making them feel good about you."