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