RE: Need some assistance with an SQL query

You should be able to just reverse the logic on the exists predicate as your update didn't change any of the values used in the subquery predicates.:

Exec Sql
Select * from RoutMaxl1 as a
Where not exists
(select 1 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);

Note I changed the correlated subquery column list to 1 instead of *. It'll be a little quicker with no change in logic.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rpglist@xxxxxxxxxxx
Sent: Friday, December 07, 2012 9: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.