Update table with Inner join and Having clause problem

[fredp1]

Hi
I 'm trying to update a header table field [ahseqn] with the maximum value of the detail field [aiseqn].
I have written the query using inner joins, but I'm struggling to convert it use 'where' statements so I can use the Update statement.
While i'm testing, I'm using the SELECT statement instead of an UPDATE statement.
I've managed to do this sort of thing in the past, but not using the HAVING clause.

the platform is DB2/400.
The 2 tables are MPAGRH(Agreement Header) The Keys are AHCONO, AHAGNB and AHSUNO
(Header company, agreement number, supplier number)
linked to MPAGRL(Agreement Line) The keys are AICONO, AIAGNB, AISUNO
(Line/Detail Company, Agreement Number, supplier number)

-- Find Agreements with the header seqn no Ahseqn, <> to the max line seqn AISEQN.
Select ahagnb, ahsuno, ahseqn, max(aiseqn)
From mpagrh t1 inner join mpagrl t2 on ahcono=aicono and ahagnb=aiagnb and ahsuno=aisuno
Group by ahagnb, ahsuno, ahseqn
Having ahseqn <> max(aiseqn)
Order by ahagnb, ahsuno, ahseqn

This is how far I have gotton to converting the query not to use Inner Joins so I can replace the Select statement with a Update statement...

Select ahagnb, ahsuno, ahseqn,
(
Select max(aiseqn)
From mpagrl t2
Where t1.ahcono=t2.aicono and t1.ahagnb=t2.aiagnb and t1.ahsuno=t2.aisuno
)
From mpagrh t1
Where exists (
Select 1
From mpagrh t3 inner join mpagrl t4 on t3.ahcono=t4.aicono and t3.ahagnb=t4.aiagnb and t3.ahsuno=t4.aisuno
Group by t3.ahcono, t3.ahagnb, t3.ahsuno, t3.ahseqn
Having t3.ahseqn <> max(t4.aiseqn) and t1.ahcono=t3.ahcono and t1.ahagnb=t3.ahagnb and t1.ahsuno=t3.ahsuno
)
Currently its repeating the key 'lots' of times.
Can someone point me in the right direction.

Thanks
Fred

 

[fredp1]

I've managed to get a simpler query to do the update for me but... the SELECT query returns the detail records.
I've ran the same query on SQL Server and it works as one would expect.
Below is the Update query that I used that worked on DB2/400 and sQl Server (without the table alias)
update mpagrh t1
set t1.ahseqn=(select max(aiseqn) from mpagrl t2 where t1.ahcono=t2.aicono and t1.ahagnb=t2.aiagnb and t1.ahsuno=t2.aisuno )
where t1.ahseqn <> (select max(t2.aiseqn) from mpagrl t2
where t1.ahcono=t2.aicono and t1.ahagnb=t2.aiagnb and t1.ahsuno=t2.aisuno.

However, if I run this select statement
select ahagnb, ahsuno, ahseqn, (select max(aiseqn) from mpagrl t2 where t1.ahcono=t2.aicono and t1.ahagnb=t2.aiagnb and t1.ahsuno=t2.aisuno ) as maxaiseqn
from mpagrh t1
where t1.ahseqn <> (select max(t2.aiseqn) from mpagrl t2
where t1.ahcono=t2.aicono and t1.ahagnb=t2.aiagnb and t1.ahsuno=t2.aisuno
)
order by t1.ahagnb, t1.ahsuno

on SQL server it works fine, but on db2/400 it returns many records.
It looks like there was a inner join from the header record to the detail record.
Can anyone tell me what could be wrong?