Updating Child Records on Import

[psvialli]

Need some help on Import and updating some data I have two tables that I need to update I can update the main table fine using code below, my problem is updating the child records ITEMS.

I have 2 tables MAIN & ITEMS main holds basic information and ITEMS holds items and notes. I import from 2 temp tables tempmain & tempitems , that can either have new records or existing records, if they are existing they will need to update the existing records. But just cannot work out how to update the Items. – main is no problem the child records is my problem.

Structure….. The temp table is exactly the same structure

MAIN
ma_pk
ma_fname
ma_sname

ITEMS
It_pk
It_ma_fk
It_Item
it_notes


SELECT tempmain
SCAN
lnclaimno=UPPER(claimno)
lnpk=pk
lnamount=amount
IF INDEXSEEK(lnclaimno,.T.,'main','claimno')
SELECT MAIN
REPLACE fname WITH tempmain.fname
REPLACE sname WITH tempmain.sname
ELSE && append a new records !
SELECT MAIN
APPEND FROM tempmain FOR claimno = lnclaimno
SELECT items
APPEND FROM tempitems FOR main_fk = lnpk
ENDIF
ENDSCAN

Any suggestion how I can update the Items Table, as there might already be 20 items and only one need to be update – the only way to find a match would be by it_item and it_ma_fk

Thanks

Paul

 

[TamarGranor]

Would something like this work for you to handle both tables:

UPDATE Main ;
FROM TempMain ;
SET fName = TempMain.fName, ;
sName = TempMain.sName ;
WHERE Main.ClaimNo = TempMain.ClaimNo

UPDATE Items ;
FROM TempItems ;
SET Notes = TempItems.Notes ;
WHERE Items.It_Item = TempItems.It_Item and ;
Items.It_Ma_FK = TempItems.It_Ma_FK

Tamar

 

[psvialli]

Many thanks it works great !

Can I ask what does the SET do ?

Thanks

Paul

 

[TamarGranor]

The command is actually UPDATE and it's SQL's version of REPLACE. The first command above says:

Update the table Main (opening it, if necessary). Base the update on date in Tempmain, matching the records based on the ClaimNo field of each of the tables. Set the fName field of Main to the corresponding fName field of TempMain and the sName field of Main to the corresponding sName field of TempMain.

For more information, check out the Update--SQL command in VFP help.

Tamar