Re: Need some assistance with an SQL query

I'm sure I'm not making myself very clear on this.

I have two PF:

xRouteMax - Contains 988 records
RouteMax - Contains over 22 mil records

I need to update RouteMax reocrds that match xRouteMax records and there
can possibly be mutliple records in RouteMax.

Both files have the same field names and length so I am qualifying them
with a for RouteMax and b for the work file xRouteMax.

I've done this several ways and I keep updating 980 records when in fact I
should be updating 988.

Here is the update statement:

Exec Sql
Update plnmaxl1 as a
Set a.mxUdt = :xDate,
a.mxUtm = :xTime,
a.mxUop = 'SPR33684',
a.mxCpdy = 'Y',
a.mxUty = 'C'
Where exists
(select * from xplnMax 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);

In this particular case I have two reoords in RouteMax that are an exact
matching except for the mxtdt field, the first record has a value
(20121015) and the second record has a value of 0; otherwise, all the
fields match. This record with the ZERO never gets updated.

I hope this helps explain my issue