Re: PL/SQL script to clean a table

Generally one brings ROWID or some other column or pseudocolumn into the fray and deletes the record with the MIN or MAX when that happens.

Daniel Morgan

Ken Denny wrote:

> hilmarcher_at_yahoo.com (Caroline) wrote in
> news:8f934c98.0204240231.5457e004_at_posting.google.com:
>
> > HI all,
> > I have a table with so many entries every day so I want to clean it.
> >
> > table name : service
> > table attributes : code, location, date
> >
> > the goal is to delete each records where :
> >
> > 1) I can find the same code AND location
> > 2) date > one month ago
> >
> > BUT : I have to keep the last record with this code and location
> > before one month.
> >
> > example:
> > 1)code=1 location=a date=20/04/02
> > 2)code=1 location=a date=20/03/02
> > 3)code=1 location=a date=21/03/02
> >
> > *I keep the record 1) beacuse the date < one month
> > *I keep the 3) and I delete the 2) record because code and location
> > are the same, date > one month
> > and 3.date > 1.date
> >
> > And I don't know how to do that ...
> >
> > Many thanks
> >
> > Caroline
>
> delete from service a where a.date < (select max(b.date) from service b
> where b.code = a.code and b.location = a.location and b.date <
> add_months(sysdate,-1));
>
> There is one problem with this though and that is if there are two rows
> with the exact same date and that is the most recent service record over a
> month old for this code and location. If that happens both will be kept
> instead of just one.
>
> --
> Ken Denny
> http://www.kendenny.com/
>
> "What does an actor know about politics?" - Ronald Reagan, complaining
> about Screen Actors' Guild president Ed Asner speaking out on US foreign
> policy
Received on Wed Apr 24 2002 - 20:03:51 CEST