Problem using update command in Foxpro

Posted on 2009-12-23
Last Modified: 2012-05-08
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?
Question by:jglapski
    LVL 41

    Expert Comment

    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

    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.
    LVL 29

    Accepted Solution

    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

    LVL 41

    Expert Comment

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

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    I recently purchased an HP EliteBook 2540p notebook/laptop. It has two video ports on it – VGA and DisplayPort. HP offers an optional docking station for the 2540p that also has both a VGA port and a DisplayPort. There are numerous online reports do…
    The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
    In this Micro Tutorial viewers will learn how to use Boot Corrector from Paragon Rescue Kit Free to identify and fix the boot problems of Windows 7/8/2012R2 etc. As an example is used Windows 2012R2 which lost its active partition flag (often happen…
    The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now