An SQL question...

I'm trying to get more proficient with AS/400 SQL.  I need to update one 
file from another, and I can't figure out if it's even possible (see below 
for why).  Once I figure out the statement, I can make it an embedded SQLRPG.

Please be aware that I didn't design these file structures, but have to 
work within them.

File 1 is a person file (most fields omitted for clarity).  A person can 
have multiple accounts, which are stored in file2 (again, most fields 
omitted for clarity).  The date fields in File1 are supposed to store the 
most recent account date.  As an account is created for a person, the 
person file should be updated with the latest date.

Due to a software bug (not to mention poor design), the person file 'most 
recent account date' fields are out of sync with the actual latest account 
date.  Normally, I would just write a level break program over file2, and 
in about fifteen lines of code, I could update the person file, and have 
everything in tip top shape.

But, the goal of todays exercise is to see if it's possible with SQL.  The 
big question is how to select the account with the highest create date, 
since the designers chose to store the date components in separate fields?

UPDATE FILE1 SET RECYY=CRTYY, RECMM=CRTMM, RECDD=CRTDD WHERE ?????

File1: (Person file, Uniquely Keyed by REFNO)
REFNO   10/0 S  Unique Key for file (Person Number)
RECYY   4/0 S   Most Recent Account Year
RECMM   2/0 S   Most Recent Account Month
RECDD   2/0 S   Most Recent Account Day

File2: (One Person Can have many Accounts)
ACCTNO  15/0 P  Unique Key for File (Account Number)
AREFNO  10/0 S  Foreign Key to File1 (Person Number)
CRTYY   4/0 S   Account Creation Date Year
CRTMM   2/0 S   Account Creation Date Month
CRTDD   2/0 S   Account Creation Date Day

Thank you.

Regards,
Rich

+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---