I read too fast. Ignore me.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Richard Kaye Sent: Monday, October 26, 2020 12:12 PM To: profoxtech@leafe.com Subject: RE: This SQL didn't update just one record but ALL records....OOPS!!!!!
You still need a WHERE clause...
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of MB Software Solutions, LLC Sent: Monday, October 26, 2020 12:06 PM To: profoxtech@leafe.com Subject: This SQL didn't update just one record but ALL records....OOPS!!!!!
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-d940f7d2f46e@mbsoft... ** 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. Report [OT] Abuse: http://leafe.com/reportAbuse/bd1a4f7b-7dcf-bfa4-340e-d940f7d2f46e@mbsoftware... _______________________________________________ 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/MWHPR1001MB2144739EAA36FA25215747A1D2190@MW... ** 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. Report [OT] Abuse: http://leafe.com/reportAbuse/MWHPR1001MB2144739EAA36FA25215747A1D2190@MWHPR1...