Hi,

I am trying to Update some fields in a VFP database through an "OLE DB
Provider for VFP" connection (from within a DTS package), specifically I
need to set the value of a field in one table to the value of a field in
another table based on a join. Now this can be done in SQL Server (or I
would think in any other ANSI SQL compliant database) using the following
syntax:

UPDATE t1
SET t1.Field1 = t2.Field1
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ForeignKey = t2.PrimaryKey
WHERE t1.Field1 > t2.Field1

But when I try to execute this statement using the OLE DB connection I get
the following error:

"Command contains unrecognized phrase/keyword"

Is there another way to what I am trying to do above to make it work?

Thanks for your help!!!

Raj
In news: ***@tk2msftngp13.phx.gbl,
Raj Parulekar <***@hotmail.com> wrote:

Hi Raj,
In case you don't have the latest VFP OLE DB provider. It's downloadable
from http://msdn.microsoft.com/vfoxpro/downloads/updates/default.aspx.
VFP has its own subset/superset of ANSI SQL. Also, in each release it's
becoming more compliant and has more features.
VFP8 (and the current OLE DB provider) does not accept the
UPDATE...FROM...JOIN syntax, although it might in future versions.
Thanks for your response Cindy, however the questions still remains as to
how can someone do the type of update that I need to do. Is there no way in
VFP currently to update fields in one table with values of a field from
another table based on a join? I guess we would not be able to use
co-related subqueries then either, right? Thanks for your response, I look
forward to your feedback. I do currently have the VFP8 OLE DB driver.

Raj
Didn't know that this is possible in sql (but it is not in VFP :-( ).
Back to VFP syntax? SET RELATION TO and a SCAN? So you could write a VFP
object that handles that and call it in your environment. You must install
more then the ole db provider, you also must install a vfp runtime.
Hi, Wim!
You wrote on Tue, 25 Nov 2003 07:50:35 +0100:

??>> I am trying to Update some fields in a VFP database through an "OLE DB
??>> UPDATE t1
??>> SET t1.Field1 = t2.Field1
??>> FROM Table1 t1
??>> INNER JOIN Table2 t2 ON t1.ForeignKey = t2.PrimaryKey
??>> WHERE t1.Field1 > t2.Field1

WdL> Didn't know that this is possible in sql (but it is not in VFP :-( ).
WdL> Back to VFP syntax? SET RELATION TO and a SCAN? So you could write a
WdL> VFP object that handles that and call it in your environment. You must
WdL> install more then the ole db provider, you also must install a vfp
WdL> runtime.

Or put this code (with SET REALTION, REPLACE or other non-SQL data
manipulation commands) to some Stored Procedure in this database and call
this SP through OLE DB link...

--
WBR, Igor