Re: update qualifying rows from join - plsql noob

On Thu, 21 Jun 2007 13:24:40 -0700, jobs <jobs_at_webdos.com> wrote:

>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!

update tabhp h
set termdate =
(select termdate
  from taban an
  where an.key = hp.key)
where exists
(select 'x'
 from taban an
 where an.key = hp.key)

When is this assingment due?

-- 
Sybrand Bakker
Senior Oracle DBA