Wiki Home

Update And Inner Join


Namespace: VFP
Coming from the sql-server world and being completly new to VFP, I tried the following to update a tables column with values of another tables column:
UPDATE [Table1] ;
   SET [Table1.SomeField] = [Table2.SomeField] ;
   FROM [Table1] INNER JOIN [Table2] ;
   ON [Table1.key]= [Table2.key]

which obviously does not work with VFP.
also some other attemps failed: e.g.,
UPDATE [Table1] ;
   SET [Table1.SomeField] = [Table2.SomeField] ;
   WHERE [Table1.key]= [Table2.key]

can anyone introduce me to the secret of how VFP joins two tables?!
grcias y saludos

Gerold

You would have to do this type of thing with xBase commands in VFP. Something like.

USE Table1 IN 0
USE Table2 IN 0
SET ORDER TO TAG tagwithkeyasexpression
USE Table1
SET RELATION TO key INTO Table2

REPLACE SomeField WITH Table2.SomeField ALL


A perhaps, more efficient example, which couldn't be killed by an OKL (On Key Label) would be this: (it also doesn't use the very handy, but less readable, LOOKUP())
USE Table2 IN 0 ORDER tagwithkeyasexpression
USE Table1 IN 0
SET RELATION TO key INTO Table2 IN Table1
REPLACE ALL Table1.SomeField WITH Table2.SomeField IN Table1 FOR Found("Table2")

-- Peter Crabtree
Or alternately, less verbose, but harder to grok
SELECT Table1
REPLACE ALL SomeField WITH LOOKUP(Table2.SomeField, Table1.ForeignKey, Table2KeyField [, Table2TagName]) AND ! EOF(Table2)

-- Steven Black (who loves LOOKUP())
I don't think you can do joins with the UPDATE command. The Hackers Guide for VFP 7 says this:
Except in a few minor details, UPDATE in Visual FoxPro acts like the Xbase REPLACE command. The SET clause takes the place of the "field WITH value" piece, and the WHERE clause fills in for the FOR clause. In fact, REPLACE seems more powerful since it also supports scope and WHILE clauses (not to mention replacing in multiple tables at once, though that's not a very good idea). On the other hand, by using a sub-query in the WHERE clause, you can do some pretty powerful things with UPDATE.
-- Steven Black
Category SQL Versions
( Topic last updated: 2002.12.16 12:31:43 PM )