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
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...
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...
Just curious, what happens if you try this?
UPDATE carriers_to_process ; SET cSession = this.cSession ; FROM carriers_to_process p1,curWorkingGroup g1 ; where g1.iid = p1.iid
That's using the old syntax inner join where the join goes in the where clause.
Peter Cushing IT Department WHISPERING SMITH
On 26/10/2020 16:05, MB Software Solutions, LLC wrote:
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 communication is intended for the person or organisation to whom it is addressed. The contents are confidential and may be protected in law. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
www.whisperingsmith.com
Whispering Smith Ltd Head Office:61 Great Ducie Street, Manchester M3 1RR. Tel:0161 831 3700 Fax:0161 831 3715
London Office: 101 St. Martin's Lane,London, WC2N 4AZ Tel:0207 299 7960