update qualifying rows from join - plsql noob

From: jobs <jobs_at_webdos.com>
Date: Thu, 21 Jun 2007 13:24:40 -0700
Message-ID: <1182457480.531965.278810@m36g2000hse.googlegroups.com>

Pardon my needing to ask and thanks in advance for any help with this.

This query produces some 20k rows.

select

       an.status,
       an.lastdate
       hp.key,
       hp.termdate
from tabhp hp inner join
     taban an on hp.keyi=an.key

where hp.termdate > sysdate
and an.status='done';

taban has one record per key with the CURRENT STATUS. tabhp has the history of key with many duplicate rows with the same keys.

the above query returns keys that are in status 'done', that have history rows with a future dated termdate. I want to correct this by setting the termdate for all those rows to the lastdate of the CURRENT STATUS ROW. so if

taban:

key=123,status=done,lastdate=1/1/2007
key=124,status=active,lastdate=1/2/2007

tabhp

key=123,termdate=1/20/2020
key=123,termdate=1/20/2020
key=124,termdate=1/20/2020

I need an update that will change tabhp as follows:

key=123,termdate=1/1/2007
key=123,termdate=1/1/2007
key=124,termdate=1/20/2020

Thank you! Received on Thu Jun 21 2007 - 15:24:40 CDT