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

I'll try that... Looks like it might work.

I was a bit wary of the DESC ordering as older versions of oracle implemented it but it didn't work. At 8.1.5 DESC indexes were implemented as function based (still didn't work at first)

Can cursors be parallelized ???

Thanx very myuch for the time and effort Andy and I'll reply to your email after the weekend to let you know if it works ...

Cheers ..... Dave.

"Andy Triggs" <andrew.triggs_at_businessobjects.com> wrote in message news:2b6e86d0.0208020659.3bf85b7a_at_posting.google.com...
> How about this piece of PL/SQL?
>
> DROP TABLE emp;
>
> CREATE TABLE emp (id NUMBER, end_date DATE);
>
> INSERT INTO emp VALUES (1, '01-JAN-1997');
> INSERT INTO emp VALUES (1, '01-JAN-1998');
> INSERT INTO emp VALUES (1, '01-JAN-1999');
> INSERT INTO emp VALUES (2, '01-JAN-1997');
> INSERT INTO emp VALUES (2, '01-JAN-1998');
> INSERT INTO emp VALUES (3, '01-JAN-1997');
> INSERT INTO emp VALUES (3, '01-JAN-1998');
> INSERT INTO emp VALUES (3, '01-JAN-1999');
> INSERT INTO emp VALUES (4, '01-JAN-1997');
>
> SELECT * FROM emp;
>
> ID END_DATE
> ======= ===========
> 1 01-Jan-1997
> 1 01-Jan-1998
> 1 01-Jan-1999
> 2 01-Jan-1997
> 2 01-Jan-1998
> 3 01-Jan-1997
> 3 01-Jan-1998
> 3 01-Jan-1999
> 4 01-Jan-1997
>
> DECLARE
> CURSOR emp_cur IS
> SELECT id FROM emp ORDER BY id, end_date DESC FOR UPDATE OF
> end_date;
> prev_emp_id NUMBER := -1;
> BEGIN
> FOR emp IN emp_cur LOOP
> IF (emp.id <> prev_emp_id) THEN
> UPDATE emp SET end_date = sysdate WHERE CURRENT OF emp_cur;
> END IF;
> prev_emp_id := emp.id;
> END LOOP;
> END;
> /
>
> SELECT * FROM emp;
>
> ID END_DATE
> ======= ===========
> 1 01-Jan-1997
> 1 01-Jan-1998
> 1 02-Aug-2002
> 2 01-Jan-1997
> 2 02-Aug-2002
> 3 01-Jan-1997
> 3 01-Jan-1998
> 3 02-Aug-2002
> 4 02-Aug-2002
>
> The id, end_date DESC ordering on the cursor ensures that the first
> record retrieved for each id is the one that needs to be updated.
>
> Note that prev_emp_id must be set to some non-null value and not be
> equal to the first id in your table in order for the first record to
> be updated correctly if its the only one for that id.
>
> Regards, Andy
>
> "Dave Siddons" <davesiddons_at_yahoo.com> wrote in message
news:<3d4a5e27_3_at_mk-nntp-1.news.uk.worldonline.com>...
> > 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 - 12:25:55 CDT