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!

On Thu, 21 Jun 2007 13:24:40 -0700, jobs <jobs@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