Re: Need some assistance with an SQL query

On 07 Dec 2012 10:50, Dan Kimmel wrote:

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);

<<SNIP>>

The query quoted above seems to select the nearly 22M rows that were not changed by the UPDATE, rather than selecting the several rows in the work-file that did not correlate to a row in the 22M row table.

AFaIK the exception join offered by Alan should have worked, but the following query I expect should also work to select the 11 rows from the work-file that would not have effected an updated row in the 22M production file:

Select b.* from xRouteMax as b
Where not exists
(select 1 from RoutMaxl1 as a
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
)