Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem updating latest value (date) column for a given key
"Dave Siddons" <davesiddons_at_yahoo.com> wrote in message news:<3d4a5e27_3_at_mk-nntp-1.news.uk.worldonline.com>...
How about using analytic function :
update t set end_date=:new_date
where rowid in
(select rid from
(select rowid rid,end_date,max(end_date) over (partition by id) latest from t)
where end_date=latest
)
HTH, Igor Izvekov.
> Sorry Dan,
>
> Not a student and this aint homework.
>
> Already have a working solution that is very similar to the one given (see
> previous post).
>
> Unfortnately the job has to update the latest (end_date) row for each ID
> (about 40000000), not individual ones, and this aint quick.
>
> If i am missing something please tell me....
>
>
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3D499F8E.2C3CFD82_at_exesolutions.com...
> > Dave Siddons 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
> >
> > It seems pretty obvious you are a student looking for someone to do your
> > homework for you ... so ... what you get is a hint rather than a solution.
> >
> > Look at the MAX function. And consider in what ways a sub-query might be
> of
> > value.
> >
> > Daniel Morgan
> >
Received on Fri Aug 02 2002 - 16:56:52 CDT