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

From: Andy Triggs <andrew.triggs_at_businessobjects.com>
Date: 2 Aug 2002 07:59:39 -0700
Message-ID: <2b6e86d0.0208020659.3bf85b7a@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 - 09:59:39 CDT