asked on

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?

Pavel Celba

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.

ASKER CERTIFIED SOLUTION

Olaf Doschke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pavel Celba

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