I read too fast. Ignore me.
--
rk
-----Original Message-----
From: ProfoxTech <profoxtech-boun...@leafe.com> On Behalf Of Richard Kaye
Sent: Monday, October 26, 2020 12:12 PM
To: profoxt...@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-boun...@leafe.com> On Behalf Of MB Software
Solutions, LLC
Sent: Monday, October 26, 2020 12:06 PM
To: profoxt...@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-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.
Report [OT] Abuse:
http://leafe.com/reportAbuse/bd1a4f7b-7dcf-bfa4-340e-d940f7d2f...@mbsoftwaresolutions.com
_______________________________________________
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/mwhpr1001mb2144739eaa36fa25215747a1d2...@mwhpr1001mb2144.namprd10.prod.outlook.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.
Report [OT] Abuse:
http://leafe.com/reportAbuse/mwhpr1001mb2144739eaa36fa25215747a1d2...@mwhpr1001mb2144.namprd10.prod.outlook.com
_______________________________________________
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/mwhpr1001mb2144056984f3593847be5269d2...@mwhpr1001mb2144.namprd10.prod.outlook.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.
