Can Update - SQL in Fox Join tables? 
Author Message
 Can Update - SQL in Fox Join tables?

FoxFolks,

In transact-SQL we can write:
    update assign inner join invoice on assignid=invid set closed =
invoice.date

In Fox I get an error.

The syntax from the Help is:

UPDATE [DatabaseName1!]TableName1
SET Column_Name1 = eExpression1
  [, Column_Name2 = eExpression2 ...]
  WHERE FilterCondition1 [AND | OR FilterCondition2 ...]]

No provision for Joins?  Or is the error mine?

Is there another way.

(Although a subquery can filter one table based on another, it can't pull
the values out of the second table to update the first.  For example
    update assign set closed = {} where assignid in (select invid from
invoice)
is ok where
    update assign set closed = invoice.date where assignid in (select invid
from invoice)
is not.



Mon, 05 Sep 2005 09:10:05 GMT  
 Can Update - SQL in Fox Join tables?
Hi John,

Please read about the LOOKUP() function in Help. Note that it will return a
blank if the value is not found and may blank out existing values unless you
test for that.

CREATE CURSOR Test1 (Field1 I, Field2 C(10))
INSERT INTO Test1 VALUES (1, "")
INSERT INTO Test1 VALUES (2, "")

CREATE CURSOR Test2 (Field1 I, Field2 C(10))
INSERT INTO Test2 VALUES (1, "Hello")
INSERT INTO Test2 VALUES (2, "World")

UPDATE Test1 ;
 SET Test1.Field2 = ;
 LOOKUP(Test2.Field2, Test1.Field1, Test2.Field1)

--
Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

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


....

Quote:
> (Although a subquery can filter one table based on another, it can't pull
> the values out of the second table to update the first.



Mon, 05 Sep 2005 12:34:42 GMT  
 Can Update - SQL in Fox Join tables?
There's no support for joins in VFP's current implementation of UPDATE.  One
way is to mix SQL and Xbase code
USE invoice IN 0
UPDATE Assign SET closed = Invoice.date ;
WHERE SEEK(Assign.assignid, 'Invoice', 'Invid'' )
"Invid" would in this example be the tag name for the index on the invid
column in Invoices

Another way is to use a local updatable view
 CREATE v_updassign AS ;
 SELECT A1.assignid, A1.closed.closed, I1.date ;
 FROM Assign A1 JOIN Invoices i1 ON A1.assignid=I1,invid
When you want to perform the update
 UPDATE v_updassign SET closed=date
and you're done.
If you've set table buffering as the default you'll have to add
 TableUpdate(1,.T., "V_updassign")

-Anders


Quote:
> FoxFolks,

> In transact-SQL we can write:
>     update assign inner join invoice on assignid=invid set closed =
> invoice.date

> In Fox I get an error.

> The syntax from the Help is:

> UPDATE [DatabaseName1!]TableName1
> SET Column_Name1 = eExpression1
>   [, Column_Name2 = eExpression2 ...]
>   WHERE FilterCondition1 [AND | OR FilterCondition2 ...]]

> No provision for Joins?  Or is the error mine?

> Is there another way.

> (Although a subquery can filter one table based on another, it can't pull
> the values out of the second table to update the first.  For example
>     update assign set closed = {} where assignid in (select invid from
> invoice)
> is ok where
>     update assign set closed = invoice.date where assignid in (select
invid
> from invoice)
> is not.



Mon, 05 Sep 2005 19:54:49 GMT  
 Can Update - SQL in Fox Join tables?
I like Anders' solutions to this problem; I used the second one he
presented, but I found I also had to do this:
        CREATE vwTEMP AS SELECT users.userid, imagefilename ;
                FROM users INNER JOIN UserPromos ;
                ON users.userid = userpromos.userid
        USE vwTEMP
        CURSORSETPROP("SendUpdates", .T.)
        UPDATE vwTEMP SET imagefilename = trim(str(userid)) + ".tif"
        USE
        DROP VIEW vwTEMP

Note the SETPROP line.

Fox's other SQL abilities are so good, it's amazing that this particular
command is so weak in this area.

------
Michael

Quote:

> Another way is to use a local updatable view
>  CREATE v_updassign AS ;
>  SELECT A1.assignid, A1.closed.closed, I1.date ;
>  FROM Assign A1 JOIN Invoices i1 ON A1.assignid=I1,invid
> When you want to perform the update
>  UPDATE v_updassign SET closed=date
> and you're done.
> If you've set table buffering as the default you'll have to add
>  TableUpdate(1,.T., "V_updassign")



Wed, 28 Sep 2005 05:45:20 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. TRYING TO UPDATE sql TABLES WITH FOX TABLES

2. how VB update Table A INNER JOIN Table B, Set A.co=B.ID

3. how VB update Table A INNER JOIN Table B, Set A.co=B.ID

4. Update w/ table joins

5. Help! Table Update based on a Join

6. update with table join

7. Update SQL 7 table from vpf6 table

8. Q: Joining 2 tables with SELECT-SQL

9. SQL question - joining two tables

10. Joining a SQL View with a DBF table

11. SQL Outer Join to Oracle 7 Tables

12. SQL OUTER JOIN TO SUM 3 TABLES?

 

 
Powered by phpBB® Forum Software