Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Andy Kramek
  Where is Andy Kramek?
 Hot Springs Village
 Arkansas - United States
 Andy Kramek
 To: Bob Stranks
  Where is Bob Stranks?
 Vancouver
 Canada
 Bob Stranks
 Tags
Subject: RE: joining files with update sql
Thread ID: 154465 Message ID: 154513 # Views: 2 # Ratings: 1
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Monday, December 17, 2007 12:59:34 PM         
   


> Hi,
>
> I am collecting the fields from one file into another with
>
> update master set master.f1=sourcefile.f1,master.f2=sourcefile.f2 from sourcefile where master.symbol=sourcefile.symbol
>
> Anyway this is working okay but it requires that the fields f1, f2 and so on are in the master file already. I am using the alter table add column to add the fields to master but this slows the process down a lot. I was wondering if "join" can be included in the above to create the f1, f2, and so on fields in master automatically during the update sql, if they are not in master but they are in sourcefile. I do not want to join the 2 files and create a 3rd file and rename it though so I am using "update: instead of a left join or right join sql.
>
> For example I would want this to occur:
>
> master file fields at start
> symbol,miscfield
>
> source file fields at start
> symbol, f1,f2,f3,f4
>
> result file master
> symbol,miscfield, f1,f2,f3,f4

I don't think that you can create columns in an update statement like that. You would have to have the columns present in order for the update to work - otherwise you will get an 'invalid column' error.

Probably the fastest way (I know you said you don't want to do it but....) is to use a query to add the columns to the master file directly and then run your update. I am pretty sure that this is going to quicker than using ALTER TABLE to add two columns:

SELECT symbol, miscfield, CAST( 0 AS INTEGER ) AS f1, CAST( '' AS VARCHAR(10) AS f2 ;
  FROM master ;
 INTO CURSOR tmpmaster READWRITE

UPDATE tmpmaster SET tmpmaster.f1 = sourcefile.f1, ;
                     tmpmaster.f2 = sourcefile.f2 ;
 FROM sourcefile WHERE tmpmaster.symbol = sourcefile.symbol


But I have to ask, why are you adding columns to a table 'on the fly' rather than just using the intermediate cursor (if it's transient) or simply defining the necessary columns (if it is persistent)?

Regards
Andy Kramek
Microsoft MVP (Visual FoxPro)
Tightline Computers Inc, Akron Ohio, USA

ENTIRE THREAD

joining files with update sql Posted by Bob Stranks @ 12/16/2007 5:38:38 PM
RE: joining files with update sql Posted by suhas hegde @ 12/16/2007 6:46:48 PM
RE: joining files with update sql Posted by Bob Stranks @ 12/16/2007 7:59:52 PM
RE: joining files with update sql Posted by Bernard Bout @ 12/17/2007 8:01:55 AM
RE: joining files with update sql Posted by Bob Stranks @ 12/17/2007 4:58:32 PM
RE: joining files with update sql Posted by Andy Kramek @ 12/17/2007 12:59:34 PM
RE: joining files with update sql Posted by Bob Stranks @ 12/17/2007 4:57:53 PM
RE: joining files with update sql Posted by Daniel Hofford @ 12/19/2007 5:12:01 AM
RE: joining files with update sql Posted by Andy Kramek @ 12/19/2007 12:06:35 PM