Re: Problem updating latest value (date) column for a given key

PS : No it aint Homework and I do have a single piece of SQL that does it but it takes hours....

Along the lines of

update emp e1
set e1.end_date = ...
where (e1.id, e1.end_date) in
(select id, max(end_date) from emp where id = e1.id group by id);

or something along those lines..

Come on sybrand.... I ain't no schoolkid, I need this to run fast.

I've been tryng to come with a way in PL/SQL that would utilise one full table scan (as a cursor and do the updates through that.)

Possibly identify the correct row (highest end_date for each id), store the rowid and then apply the change via that. Worried about rollback requirements though.

Keep on thinkin ...........

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:o3uikukppud9094it145c5bds0nnbu5nfg_at_4ax.com...
> On Thu, 1 Aug 2002 16:06:36 +0100, "Dave Siddons"
> <davesiddons_at_yahoo.com> wrote:
>
> >Hi,
> >
> >I have 2 relevant columns
> >
> >ID and END_DATE
> >
> >For each person in this table (identified by ID) there will many rows in
the
> >table, each representing a position held within the company. Each
END_DATE
> >is the END_DATE representing the date they left that position. I need a
> >fast way (SQL or PL/SQL with or without new indexes) to update the latest
> >END_DATE for each ID in the table.
> >
> >Any ideas anyone.
> >
> >Thanks for any help received .....
> >
> >Dave
> >
> >
> >
> update emp
> set ... = ...
> where id = :b1
> and end_date =
> (select max(end_date)
> from emp e1
> where e1.id = emp.id
> )
>
> Homework.
>
> Hth
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Thu Aug 01 2002 - 14:25:37 CDT