update values in table A from values in table B 
Author Message
 update values in table A from values in table B

Is there a way to update field1 in table A from field1 in Table B.  Both
tables have a common field for a relation.

Access has an update query, is there anything similar in VFP 6.

Thanks in advance.

R.



Thu, 28 Apr 2005 10:03:30 GMT  
 update values in table A from values in table B

Quote:
> Is there a way to update field1 in table A from field1 in Table B.
> Both tables have a common field for a relation.

Bob,

Please read about the REPLACE command in Help. Note especially why the "IN
ParentCursor" in the following example is necessary. Also try the code
without the "FOR ParentCursor.Pk = ChildCursor.Fk" to see why it is
necessary.

CREATE CURSOR ParentCursor (Pk I, Col2 C(10))
INSERT INTO ParentCursor VALUES (1, "Update Me")
INSERT INTO ParentCursor VALUES (2, "Update Me")
INSERT INTO ParentCursor VALUES (3, "Update Me")

CREATE CURSOR ChildCursor (Fk I, Col2 C(10))
INSERT INTO ChildCursor VALUES (1, "Update 1")
INSERT INTO ChildCursor VALUES (3, "Update 3")
INSERT INTO ChildCursor VALUES (4, "Update 4")
INDEX ON Fk TAG Fk

SELECT ParentCursor
SET RELATION TO Pk INTO ChildCursor

REPLACE ALL ParentCursor.Col2 ;
 WITH ChildCursor.Col2 ;
 FOR ParentCursor.Pk = ChildCursor.Fk ;
 IN ParentCursor

--

Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

http://msdn.microsoft.com/vfoxpro  http://foxcentral.net



Fri, 29 Apr 2005 04:20:58 GMT  
 update values in table A from values in table B
Thanks for the help Cindy, I missed the "IN" section - I guess reading does
pay off....


Quote:


> > Is there a way to update field1 in table A from field1 in Table B.
> > Both tables have a common field for a relation.

> Bob,

> Please read about the REPLACE command in Help. Note especially why the "IN
> ParentCursor" in the following example is necessary. Also try the code
> without the "FOR ParentCursor.Pk = ChildCursor.Fk" to see why it is
> necessary.

> CREATE CURSOR ParentCursor (Pk I, Col2 C(10))
> INSERT INTO ParentCursor VALUES (1, "Update Me")
> INSERT INTO ParentCursor VALUES (2, "Update Me")
> INSERT INTO ParentCursor VALUES (3, "Update Me")

> CREATE CURSOR ChildCursor (Fk I, Col2 C(10))
> INSERT INTO ChildCursor VALUES (1, "Update 1")
> INSERT INTO ChildCursor VALUES (3, "Update 3")
> INSERT INTO ChildCursor VALUES (4, "Update 4")
> INDEX ON Fk TAG Fk

> SELECT ParentCursor
> SET RELATION TO Pk INTO ChildCursor

> REPLACE ALL ParentCursor.Col2 ;
>  WITH ChildCursor.Col2 ;
>  FOR ParentCursor.Pk = ChildCursor.Fk ;
>  IN ParentCursor

> --

> Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

> http://msdn.microsoft.com/vfoxpro  http://foxcentral.net



Fri, 29 Apr 2005 04:54:46 GMT  
 update values in table A from values in table B
Hi, Bob101!
You wrote  on Sat, 9 Nov 2002 18:03:30 -0800:

 B> Is there a way to update field1 in table A from field1 in Table B.
 B> Both tables have a common field for a relation.

 B> Access has an update query, is there anything similar in VFP 6.

No, there is no simple way. Such type of Update statement as in Access is
not yet supported in VFP. But there is some other ways:
1) Open both tables, set relation from one to another and then issue REPLACE
ALL command.
2) Use LOOKUP () function in REPLACE ALL command.
3) Use SCAN ALL with SEEK and REPLACE inside the loop.
4) Maybe some other not so simple...

It is common question, so you can find MANY code samples if you'll search in
Google Groups.

WBR, Igor



Fri, 29 Apr 2005 06:44:30 GMT  
 update values in table A from values in table B
Anders, Igor  thanks for the info, Cindy's solution (below) works exactly
like the Access Update query

Bob.


Quote:


> > Is there a way to update field1 in table A from field1 in Table B.
> > Both tables have a common field for a relation.

> Bob,

> Please read about the REPLACE command in Help. Note especially why the "IN
> ParentCursor" in the following example is necessary. Also try the code
> without the "FOR ParentCursor.Pk = ChildCursor.Fk" to see why it is
> necessary.

> CREATE CURSOR ParentCursor (Pk I, Col2 C(10))
> INSERT INTO ParentCursor VALUES (1, "Update Me")
> INSERT INTO ParentCursor VALUES (2, "Update Me")
> INSERT INTO ParentCursor VALUES (3, "Update Me")

> CREATE CURSOR ChildCursor (Fk I, Col2 C(10))
> INSERT INTO ChildCursor VALUES (1, "Update 1")
> INSERT INTO ChildCursor VALUES (3, "Update 3")
> INSERT INTO ChildCursor VALUES (4, "Update 4")
> INDEX ON Fk TAG Fk

> SELECT ParentCursor
> SET RELATION TO Pk INTO ChildCursor

> REPLACE ALL ParentCursor.Col2 ;
>  WITH ChildCursor.Col2 ;
>  FOR ParentCursor.Pk = ChildCursor.Fk ;
>  IN ParentCursor

> --

> Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

> http://msdn.microsoft.com/vfoxpro  http://foxcentral.net



Sat, 30 Apr 2005 06:59:28 GMT  
 update values in table A from values in table B
Bob
There's a workaround
CREATE VIEW updatetables AS SELECT A.idfield, A.field1 AS target, B.field1
AS source ;
FROM TableA AS A JOIN TableB AS B ON A.idfield=B.idfield ;
WHERE date>=?ldlDate
or whatrever condition would be generally applicable.
Use the View Designer's Update Criteria tab to set idfield as Keyfield and
A.field1 as updatable and check SendUpdates.

To update
 OPEN DatabASE X
   UPDATE updatetables SET target=source

 -Anders


Quote:
> Is there a way to update field1 in table A from field1 in Table B.  Both
> tables have a common field for a relation.

> Access has an update query, is there anything similar in VFP 6.

> Thanks in advance.

> R.



Fri, 29 Apr 2005 23:50:46 GMT  
 update values in table A from values in table B
Yes it does. As it requires an index on the sourece table yuu can also do
this, assuming the fk index tag is called 'Fk'
Use Child
UPDATE Parent SET col2 = Child.Col2 WHERE SEEK(Parent.pk, 'Child', 'Fk' )
-Anders


Quote:
> Anders, Igor  thanks for the info, Cindy's solution (below) works exactly
> like the Access Update query

> Bob.





> > > Is there a way to update field1 in table A from field1 in Table B.
> > > Both tables have a common field for a relation.

> > Bob,

> > Please read about the REPLACE command in Help. Note especially why the
"IN
> > ParentCursor" in the following example is necessary. Also try the code
> > without the "FOR ParentCursor.Pk = ChildCursor.Fk" to see why it is
> > necessary.

> > CREATE CURSOR ParentCursor (Pk I, Col2 C(10))
> > INSERT INTO ParentCursor VALUES (1, "Update Me")
> > INSERT INTO ParentCursor VALUES (2, "Update Me")
> > INSERT INTO ParentCursor VALUES (3, "Update Me")

> > CREATE CURSOR ChildCursor (Fk I, Col2 C(10))
> > INSERT INTO ChildCursor VALUES (1, "Update 1")
> > INSERT INTO ChildCursor VALUES (3, "Update 3")
> > INSERT INTO ChildCursor VALUES (4, "Update 4")
> > INDEX ON Fk TAG Fk

> > SELECT ParentCursor
> > SET RELATION TO Pk INTO ChildCursor

> > REPLACE ALL ParentCursor.Col2 ;
> >  WITH ChildCursor.Col2 ;
> >  FOR ParentCursor.Pk = ChildCursor.Fk ;
> >  IN ParentCursor

> > --

> > Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

> > http://msdn.microsoft.com/vfoxpro  http://foxcentral.net



Sat, 30 Apr 2005 11:07:05 GMT  
 update values in table A from values in table B
Anders,
The UPDATE solution eliminates a few steps (like the relation)  thanks!
Bob.


Quote:
> Yes it does. As it requires an index on the sourece table yuu can also do
> this, assuming the fk index tag is called 'Fk'
> Use Child
> UPDATE Parent SET col2 = Child.Col2 WHERE SEEK(Parent.pk, 'Child', 'Fk' )
> -Anders



Sat, 30 Apr 2005 11:30:30 GMT  
 update values in table A from values in table B
There's another problem with this whole operation. If there's a 1:M
relationship between Parent and Child, which row in Child will supply the
new value in Parent

Parent
1, "w"
Child
1, 'x'
1, 'y'
1, 'z'

Seek and Set Relation into Child will find 1, 'x'
If the relation is turned around or the Fk index in Child is changed to SET
ORDER TO Fk IN Child DESCENDING then the result will be 1, 'z'

-Anders


Quote:
> Anders,
> The UPDATE solution eliminates a few steps (like the relation)  thanks!
> Bob.



> > Yes it does. As it requires an index on the sourece table yuu can also
do
> > this, assuming the fk index tag is called 'Fk'
> > Use Child
> > UPDATE Parent SET col2 = Child.Col2 WHERE SEEK(Parent.pk, 'Child',
'Fk' )
> > -Anders



Sat, 30 Apr 2005 19:42:02 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. change value in table depending on values in other table

2. grid.txt.values VS. table.values

3. UPDATE using value from another table

4. Validate TextBox Value Before Table Update

5. Recalculating table with values from other table

6. Insert values from one table into another table.

7. Please help me update the values in one table with values from another table....

8. Summing values from 2 related tables in one SQL statement

9. insert dbf values into a table

10. saving RTF control value into SQL7 table

11. Add field to a new table based on Varibles values

12. Field value in table changes mysteriously

 

 
Powered by phpBB® Forum Software