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
_______________________________________________
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/640bb4bb-51df-c657-be70-51df481a2...@whisperingsmith.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.
