How to do a SQL UPDATE in VFP?

[Raj Parulekar]

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

[Cindy Winegarden]

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

Hi Raj,

Post by Raj Parulekar
I am trying to Update some fields in a VFP database through an "OLE DB
Provider for VFP" connection (from within a DTS package),

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.

Post by Raj Parulekar
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)

VFP has its own subset/superset of ANSI SQL. Also, in each release it's
becoming more compliant and has more features.

Post by Raj Parulekar
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
"Command contains unrecognized phrase/keyword"

VFP8 (and the current OLE DB provider) does not accept the
UPDATE...FROM...JOIN syntax, although it might in future versions.

[Raj Parulekar]

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

Post by Cindy Winegarden
Hi Raj,

Post by Raj Parulekar
I am trying to Update some fields in a VFP database through an "OLE DB
Provider for VFP" connection (from within a DTS package),

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.

Post by Raj Parulekar
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)

VFP has its own subset/superset of ANSI SQL. Also, in each release it's
becoming more compliant and has more features.

Post by Raj Parulekar
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
"Command contains unrecognized phrase/keyword"

VFP8 (and the current OLE DB provider) does not accept the
UPDATE...FROM...JOIN syntax, although it might in future versions.
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP

[Wim de Lange]

Post by Raj Parulekar
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

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.

[Igor Korolyov]

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