Delete Update Query 1

[pdsterling]

All the posters out there did me a great boost when I got XP and had so much trouble getting going. I am on a new problem, and just can't seem to hack it. Your advice would be gratefully appreciated.

What I need to do in 25 words or less is:

Take File0001 and File0002,
If KeyValue is present in File0001 and not present in File0002, mark that record, in File 0001, for deletion.

I seem to remember doing an operation like this in Paradox, but just one time and under stress.

TIA, pds

 

[dbMark]

If the tables are not indexed, this will work but quite slowly if the tables are large. If those fields are indexed or you create indexes for them, it will run much faster using the SEEK() function.

******************************************************************
SET EXACT ON
SET NEAR OFF
USE File0001 IN 1
USE File0002 IN 2
SELECT File0001
@ 3,5 SAY ALIAS(1)+" has"+STR(RECCOUNT(ALIAS(1)),8)+" records."
@ 4,5 SAY ALIAS(2)+" has"+STR(RECCOUNT(ALIAS(2)),8)+" records."
nFailCnt = 0
SCAN
   SELECT File0002
   LOCATE FOR File0001->myField = File0002->myField 
   SELECT File0001
   IF EOF("File0002")
      DELETE
      nFailCnt = nFailCnt + 1
   ENDIF
   @ 6,5 SAY "Processing Record "+LTRIM(STR(RECNO(),8)) ;
            +" with "+LTRIM(STR(nFailCnt,8))+" records deleted..."
ENDSCAN
RETURN

 

[pdsterling]

Thanks for the tips! I have never really used SCAN at all, but see at a glance how this could work. Do I send roses or Hershey bars?

P D Sterling