This SQL didn't update just one record but ALL records....OOPS!!!!! - ProFox

26 Oct 2020


      VFP9SP2 using DBC tables.
The curWorkingGroup cursor had just ONE record in it.  The 
Carriers_To_Process table (PK = IID field) has tens of thousands. The 
idea is that I'm just processing small sections at a time from 
Carriers_To_Process.  Rather than update the one or two records in the 
curWorkingGroup cursor that are a subset of the Carriers_To_Process, it 
updated ALL of the records in Carriers_To_Process, basically not 
respecting the JOIN condition.
UPDATE carriers_to_process ;
                        SET cSession = this.cSession ;
                       FROM carriers_to_process p1 ;
                      INNER JOIN curWorkingGroup g1 on g1.iid = p1.iid
What's wrong with my code for this simple UPDATE SQL ?
Here's the full segment to put it in context (with curToDo being the 
subset of Carriers_To_Process and curUniques being the unique ven_id 
field (so that we don't process a ven_id twice):
SELECT COUNT(*) FROM curUniques WITH (BUFFERING=.T.) 
WHERE lSelected INTO ARRAY laCnt
                 liTotal = NVL(laCnt[1],0)
                 SELECT curUniques
                 SCAN FOR lSelected && FOR EMPTY(tProcessed) AND 
EMPTY(tError) AND EMPTY(tignored) AND ondemand = m.tlOnDemand
                     liCnt = liCnt + 1
                     _screen.Caption = "Processing " + 
ALLTRIM(curUniques.ven_name) + " (" + ALLTRIM(STR(curUniques.ven_id)) + 
") - " + ALLTRIM(STR(liCnt)) + " of " + ALLTRIM(STR(liTotal))
                     *** mjb 10/23/2020 - mark the session for the ones 
we're processing so that subsequent runs aren't grabbing them from the 
initial query above
                     SELECT iid ;
                     FROM curToDo ;
                     WHERE ven_id = curUniques.ven_id ;
                      INTO CURSOR curWorkingGroup READWRITE
UPDATE carriers_to_process ;
                        SET cSession = this.cSession ;
                       FROM carriers_to_process p1 ;
                      INNER JOIN curWorkingGroup g1 on g1.iid = p1.iid
IF this.GetRecords('tmp',curUniques.ven_id) = 1 THEN
                         UPDATE carriers_to_process ;
                            SET tProcessed = DATETIME() ;
                           FROM carriers_to_process p1 ;
                          INNER JOIN curWorkingGroup g1 on g1.iid = p1.iid
                     ELSE
                         UPDATE carriers_to_process ;
                            SET tError = DATETIME() ;
                           FROM carriers_to_process p1 ;
                          INNER JOIN curWorkingGroup g1 on g1.iid = p1.iid
                     ENDIF && this.GetRecords('tmp',curUniques.ven_id)
                     USE IN SELECT('curWorkingGroup')
                 ENDSCAN && carriers_to_process
I can revert to a simple xBASE approach with SCAN and SEEK/REPLACE but 
figured the UPDATE SQL was more elegant and easier to view.  ;-)
tia,
--Mike
-- 
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus