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
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: https://leafe.com/archives
This message:
https://leafe.com/archives/byMID/bd1a4f7b-7dcf-bfa4-340e-d940f7d2f...@mbsoftwaresolutions.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
