Updating a table from a free table

[trideryck]

I am using a VFP 6.0 I inherited with the job and am having a little trouble updating it.

The table I want to update has over 100,000 records and uses the primary key partnum. I only want to update one field in the table (i.e. price), and only for those records where partnum matches partnum in my free table.

Any help would be appreciated. At this juncture I'm not very familiar with VFP so basic instructions would be well received. Thanks!

 

[DSummZZZ]

Let's say the table you want to update is named udtable and the partnum tag has been created from partnum field. The free table is named well, freetable. The best thing to do would be to also have an index on the partnum field in the free table. If not, use these statements to create one:

USE freetable EXCLUSIVE
INDEX ON partnum TAG partnum
USE

Then you can use something like this:

SELECT 0
USE freetable ORDER partnum
SELECT 0
USE udtable ORDER partnum
*... scan through each record of udtable
SCAN  
   *... if you find it, update the price
   IF SEEK(udtable.partnum, 'freetable')  
      REPLACE price WITH freetable.price
   ENDIF
ENDSCAN
CLOSE ALL

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[TheRambler]

There are many ways to do it, you may want to try this:

1. make a backup copy of your table to update
2. create an index on partnum for your free table
USE freeTable
INDEX ON partnum TAG partnum
3. open your table to update
USE yourTable IN 0
4. establish a relationship between both tables
SET RELATION TO partnum INTO freeTable
5. update the field
REPLACE ALL price WITH freeTable.price FOR NOT EOF("freeTable")

 

[trideryck]

When I add either approached into a program and run it, VFP cannount find the variable 'partnum'. Where and how do I declare this in either of the examples?

 

[TamarGranor]

Are you sure both tables have a field named PartNum?

Tamar

 

[trideryck]

Okay, initially I had the table 'original' in my database, and the table 'newdata' as a free table in my database. Both have partnum as a field. I went ahead and added newdata as a table versus a free table. Doing that gives rid of the variable not found. However, now...

When I run the code Dave Summers provided above using SCAN, it firsts asks:
"partnum already exists, overwrite it?" Whether I choose yes or no, my data isn't updated. (I've checked spellings on the fields, etc. already)

When I run the code TheRambler provided, I first am asked "partnum already exists, overwrite it?" Whether I choose yes or no, the program stops on the line:

SET RELATION TO partnum INTO freeTable

and says it's a cyclic relation. Any ideas would be appreciated. I've been trying to find a 'shortcut' to updating a single field in select records for a while now.

 

[DSummZZZ]

It doesn't matter whether either of those tables are in the database or not, what's more important is the names of the tables and the fields.
I was just trying to show an example giving generic table and field names. You need to substitute them appropriately.
As for the "partnum already exists, overwrite it?" message, VFP is only asking if you want to overwrite the index already created. Once that index has been created, you can ignore or even remove that section of code related to creating the index.

SELECT 0
USE [COLOR=red]newdata[/color] ORDER partnum
SELECT 0
USE [COLOR=red]original[/color] ORDER partnum
*... scan through each record of udtable
SCAN  
   *... if you find it, update the price
   IF SEEK([COLOR=red]original[/color].partnum, '[COLOR=red]newdata[/color]')
      REPLACE price WITH [COLOR=red]newdata[/color].price
   ENDIF
ENDSCAN
CLOSE ALL

Keep in mind that in order for the SEEK to work, the data must be the same value and length. So you may have to do some other things if it doesn't work properly.
You may need to post field sizes and sample data.

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[trideryck]

Well, when both tables weren't in the database, I was getting "VARIABLE parnum not found', and I wasn't sure how to fix that.

As for my tables, they have identical fields and lengths.

both original and newdata tables only contain fields:
partnum (integer, index ascending)
price (character, length 25)
location (character, length 25)

sample data for original:
partnum price location
98001 3.25 Shelf 7:Rack 2
121002 N/A N/A
121003 27.25 Shelf 3:Rack 1
121004 on order on order

sample data for newdata:
partnum price location
98001 on order on order
121004 12.95 Shelf 4: Rack 1

This hopefully illustrates that what I'm trying to do is only update 2 records (the ones where partnum is held in common). And if I've forgotten to mention it, thanks for everyone's help thus far!

 

[DSummZZZ]

I just created two tables named 'original' and 'newdata' and created an index on both tables using this command:
INDEX ON partnum TAG partnum.

I then added the data you listed above and ran the routine I posted unmodified. It worked properly.
So then I added the table named 'original' to a database, left 'newdata' as a free table, and reran the same code unmodified. It again worked properly.

So unless there is something else you're not mentioning, you should be having no problem with that piece of code.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[trideryck]

Heh. I recreated everything from scratch and it worked for me too. The problem was that my initial database was not in the default path as my VFP98 program.

It worked when I moved everything to my default path:
C:\Program Files\Microsoft Visual Studio\Vfp98

So...I guess my question now is how do I make it look for all my tables/databases in another folder than this one?

Other than that, thank you very much for your patience and expertise!

 

[DSummZZZ]

You can preface the file names with the full path:

USE C:\MyAppDir\Data\original INDEX partno

Just remember to enclose it in quotes if there are spaces in the path:

USE "C:\Program Files\MyAppDir\Data\original" INDEX partno

Or a slightly less safe method is to use the SET PATH command:

SET PATH TO C:\MyAppDir\Data\

This will work fine provided you have no other files (tables) by the same name in your search paths. VFP will open the first one it finds.
You can then just 'USE' the files as normal.

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm