• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 481
  • Last Modified:

Problem using update command in Foxpro

I am trying to update a cursor from another cursor using the following update command:

upda file1 set file1.field1=file2.field1 wher betw(file1.rec, file2.begrec, file2.endrec)

"rec" is a field that has been replaced by recno()

"begrec" is the beginning record, and "endrec" is the ending record, so that it updates file1 from file2, based on a range between two records. However, nothing is getting replaced. Is this type of criteria able to work in Foxpro?
0
jglapski
Asked:
jglapski
  • 2
1 Solution
 
pcelbaCommented:
This kind of update query is very nonstandard. You should not rely on RECNO() because your table can contain deleted records, the RECNO() sequence will be broken and the update will be unpredictable.

But let say you don't have deleted records or you have all of them visible (SET DELETED OFF). In such case you may use standard xBase language:

USE file1 IN 0
USE file2 EXCLUSIVE IN 0
SELECT file2
INDEX ON RECNO() TAG RecNum  && Index needed for relation establishing
SELECT file1
SET RELATION TO rec INTO file2
REPLACE field1 WITH file2.field1 FOR BETWEEN(rec, 1, 100) AND !EOF("file2")

Another possibility is to do the update in a loop (without index):

SELECT file1
SCAN FOR BETWEEN(rec, 1, 100)
  GOTO RECORD (file1.rec) IN file2
  REPLACE file1.field1 WITH file2.field1
ENDSCAN

All above examples suppose the file1.rec field is populated by appropriate record number from file2. Constant range of records use in BETWEEN function can be changed to variables, of course.
0
 
Olaf DoschkeSoftware DeveloperCommented:
Your update does miss a FROM clause. And if you would add it, you can't read file2.begrec and file2.endrec from one record of file2 and at the same time traverse file2 records to update file1, in short your logic is ill.

If you really want to update a range of file1 records rom the same range of file2 records, you'll first need to store beginrec and endrec in a variable each, then do your logic this way...

However then I assume your two files are a vertical split table with 1:1 records and you need to be cautious, that their record number really relates the right records to each other. It's recommended to have an ID column in both file1 and file2 then to have a stronger and more mandatory relation

Bye, Olaf.

PS: @pcelba: you can relate two dbfs based on the recno without any index.
use file1 in 0
use file2 in 0

* locate file2 record determining the record range
lnBeginrec =  file2.begrec
lnEndrec = file2.endrec

select file1
set relation to recno() into file2
replace field1 with file2.field1 for between(recno(),lnStartrec,lnEndrec) in file1

Open in new window

0
 
pcelbaCommented:
Yes, I know it (maybe) but the reason why there is rec column in file1 is most probably different record numbers for update.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now