RE: Need some assistance with an SQL query

Off the top of my head try the following
(select b.* from
xRouteMax as b
exception join RoutMaxl1 as a
on a.mxplan = b.mxplan and
a.mxdiv = b.mxdiv and
a.mxins = b.mxins and
a.mxben = b.mxben and
a.mxppo = b.mxppo and
a.mxtfrm = 'V' and
a.mxtdt = 0);

Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rpglist@xxxxxxxxxxx
Sent: Friday, December 07, 2012 10:22 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Need some assistance with an SQL query

I have two files, one is a work file with 991 records and the other is a production (Test Environment) file with 22 mil. I am updating the production records that match to records in the work file.

Here is my sql statement:
Exec Sql
Update RoutMaxl1 as a
Set a.mxUdt = :xDate,
a.mxUtm = :xTime,
a.mxUop = 'CPP989825645KY',
a.mxCpdy = 'Y',
a.mxUty = 'C'
Where exists
(select * from xRouteMax as b
where a.mxplan = b.mxplan and
a.mxdiv = b.mxdiv and
a.mxins = b.mxins and
a.mxben = b.mxben and
a.mxppo = b.mxppo and
a.mxtfrm = 'V' and
a.mxtdt = 0);

The statement runs fine but I only update 980 records, which means that I have 11 records in table A (xRouteMax) that do not exist in table B.

Suggestion on how to get a list of those records? I just want the 11.





--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.


Disclaimer: This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.