Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem updating latest value (date) column for a given key

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

From: Igor Izvekov <igoriz_at_cmtk.net>
Date: 2 Aug 2002 14:56:52 -0700
Message-ID: <9f17469e.0208021356.329d3190@posting.google.com>


"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US