Link to home
Start Free TrialLog in
Avatar of Dee
DeeFlag for United States of America

asked on

Need help with logic/syntax/plan of attack in updating table from another table

I have 2 tables that look like this:

[embed=file 1416224

There is a 1:1 relationship based on PRRecno            
                  
The scrub file is used to fix/conform matching field names and contents in the pull file.                              

The contents of the all fields in the scrub file will replace the contents in the matching fields in the pull file.            
                  
The scrub file fields will overwrite the pull file fields, regardless of whether they are the same values or not.
                        
DefaultDir is the location of the pull file.  The pull file is the only file in the defaultDir.                              
                               
My initial thought was to get a count of the number of records based on defaultdir  in the Scrub  file that will match to the pull file.

I need more than that.  Here is my messy start:

-------------------------------------------------------------------------------------------------------------------
select defaultdir, count(*) as cnt from ckBreedScrub order by defaultdir group by defaultdir into cursor curBR1
*This gives the number of records per defaultdir ... not sure if I need this or how to get what I need
&& and/or something like this (it does not run...)
------------------------------------------------------------------------------------------------------------------

select * from ckBreedScrub order by defaultdir, prrecno into cursor curBR2

lcFName = "\PullFile.dbf"

scan                                                                                                                                                                                                                                                                                                         
      if RECCOUNT("curBR2") > 0
            
            lcPracDir = alltrim(defaultdir) + "\"  
            lcFile = (lcPracDir) + (lcFname)
            *? set relation into LCFile
            lcRecno = PRRecno
            Replace all lcFile.Breed with Breed for PRecno = lcFile.Recno  &&(breed field, etc) &&syntax error
            && ? Find Matching PRRecno in Pull File
            && ? Update Pull File records from cursor
      endif
endscan

Thank you.
VET.jpg
Avatar of Dee
Dee
Flag of United States of America image

ASKER

The scrub file will probably have no more than about 300 records that match to the pull file in no more than 50 locations. (defaultdir)

The pull files will average about 2000 records.
ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dee

ASKER

pcelba!      My hero!      You are amazing!  

Thank you!  🌹
Avatar of Dee

ASKER

It's perfect!   :)  Thanks so much.