Update a table with another table

[DiGi2]

Thank you in advance for any help. I have not used foxpro since the mid 80s. I have not even programmed in over 10 years but I have a problem and want to use VFP.

I have 2 tables with different structures; CP and Inspections. Both have an orderno.
I want to use table CP and loop through it
If the record is found in Inspections then replace with values from CP

does someone have an example of a similar program ?

I am not sure if I do a do while not EOF then a scan, endscan ?

 

[mmerlinn]

[ ]

One possible way would be to do something like this (untested):

[TT][BLUE]
SELECT inspections.orderno, inspections.blah ;

FROM inspections, cp ;
WHERE inspections.orderno = cp.orderno ;
INTO aArray ;
ORDER BY inspections.orderno​

SELECT cp

FOR z = 1 TO ALEN(Array)

REPLACE field2 WITH aArray[z, 2], field2 WITH aArray[z, 2], fieldn WITH aArray[z, n] ;​

FOR orderno = aArray[z, 1]​

ENDFOR

RELEASE aArray
[/BLUE][/TT]

This assumes there is ONLY UNIQUE records in both tables. If not, this will produce garbage for you.

There are probably better and faster ways of doing this, but something like this will do the job.


mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, who are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond

 

[Mike Gagnon]

Is this correct?
REPLACE field2 WITH aArray[z, 2], field2 WITH aArray[z, 2],

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[DiGi2]

I had an old Visual Foxpro book and I remember seein g a program that did this. Unfortunatley I dont have the book anymore.
I used to keep a library of code snippets that did certain tasks. The one I had went something like this

Use inspections in 0
use CP order orderno in 1
sele inspections

scan
m.orderno=orderno
if seek(m.orderno,1)
replace inspections.date with cp.date
replace inspections .inspector with cp.employee
endif
endscan

It had this basic structure but I dont know if the syntax is correct.
Has anyone ever had to do something like this ?

Thanks agains for all your help.

 

[Dan Freeman]

If you've saved that code snippet, throw it away.

*NEVER* explicitly name a workarea when opening a table.
*NEVER* use multiple REPLACE commands when it can be done in a single command.

I'd go a little old school here, probably.

Use CP in 0
Use inspections Order orderno in 0
Select CP
Set Relation to orderno into inspections
Replace all (inspections.field with cp.field, etc.)
Use in CP
Use in inspections

Any record in CP that has a matching record in inspections will update inspections. The other records will point to EOF in inspections and update nothing. The ALL scope on REPLACE is the equivalent of a SCAN without requiring multiple lines of code.

 

[Olaf Doschke]

What version of VFP do you use? In VFP9 you can use an SQL Update query to update one table from another one:

Update CP
Set field1 = Inspections.field1, field2 = Inspections.field2... etc
From Inspections
Where CP.orderno = Inspections.orderno

The one line having the fields to update, is what's Replace all (inspections.field with cp.field, etc.) is in Dan's code. It's of course up to you which source fields of Inspections go into which CP fields, and in both my and dan's version you're of course free to also use expressions to compute something, concatenate strings or whatever.

_TALLY will tell you how many records where updated with the query, that's also true for the REPLACE ALL of Dan.

Bye, Olaf.

 

[DiGi2]

Thanks Olaf

Is that feature not available in VFP 8.0 ?

I had never used Dan's way either. I will give his a try if I cant update to VFP 9.
I will have to research the replace all command

Can you even buy VFP 9 anymore?

 

[Dan Freeman]

There's nothing about Olaf's UPDATE command that would preclude using it VFP8.

 

[Olaf Doschke]

Dan, most probably you're right. DiGi2, just try, the worst thing that can happen is you get a syntax error, then you know.

I skipped VFP8 and I know this does not work in VFP7 and I can guarantee this works in VFP9.

Bye, Olaf.

 

[DiGi2]

I did try it and it didnt work. Does the sytax look ok ?

2 tables
jan2report (column2 is field name and has control #)
cp1231 ( wordnumb is control #)

Update CP1231 Set inspector = jan2report.column1
From jan2report
Where Jan2report.column2 = Cp1231.wordnumb

I get an error unrecognized command

 

[DiGi2]

I also tried it Dan's way with some test data but I got "unrecognized phrase or keyword"

Use jan2report order column2 in 0
Use cp1231 Order wordnumb in 0
Select cp1231
Set Relation to wordnumb into jan2report
Replace all (cp1231.inspector with jan2report.column1)
Use in cp1231
Use in jan2report

can you see where my error is?

 

[DiGi2]

Thanks for your help
I did get it to work with this code. Not artful but effective. It updated about 75,000 records in less than a second

USE CP1231 IN 0
USE JAN2REPORT IN 0
select JAN2REPORT && values come FROM this table
scan
update cp1231 ;
set inspector = JAN2REPORT.column1 ;
where wordnumb = JAN2REPORT.column2
endscan
USE IN CP1231
USE IN JAN2REPORT

 

[JRB-Bldr]

Another way to do this without using the record-by-record SCAN/ENDSCAN might be to use a RELATION between the two data tables, based on the fields whose values you want to match.

USE JAN2REPORT IN 0 EXCLUSIVE
SELECT JAN2REPORT
INDEX ON column2 TAG Key

USE CP1231 IN 0
SELECT CP1231
SET RELATION TO wordnumb INTO JAN2REPORT
REPLACE ALL CP1231.inspector WITH JAN2REPORT.column1
   FOR !EMPTY(JAN2REPORT.column2)
CLOSE DATA ALL

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

USE CP1231 IN 0
USE JAN2REPORT IN 0
select JAN2REPORT && values come FROM this table
scan
update cp1231 ;
set inspector = JAN2REPORT.column1 ;
where wordnumb = JAN2REPORT.column2
endscan
USE IN CP1231
USE IN JAN2REPORT

Remove the lines SCAN and ENDSCAN, then it will work in even less time- Because what you did is execute the UPDATE as many times as you have records in JAN2REPORT. But one update updates the whole table. An Update SQL or SELECT SQL works on a whole table (or join of tables). There can be situations you scan a third table, but mostly SQL does not combine with SCAN..ENDSCAN, it's an opertion on the full data anyway. I didn't say you need any SCAN..ENDSCAN, jus the Update.

Your initial try just was missing the line continuation character ";", and sorry, my code did miss that, too. I tend to forget this, as I send queries to SQL Server mostly and then don't need such line continuation chars.

Bye, Olaf.

 

[Olaf Doschke]

...what you also don't need to do is open tables and select JAN2REport, when tables are in the current dir or in the currently set database, the last two lines closing the CP1231 and JAN2REPORT tables could stay, but you also could let the datasession ending later close the tables automatically when your form releases, or when you quit VFP. You really just need the Update-SQL command, with semicolons added, but no more and no less.

Bye, Olaf.