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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: SQL help: delete recs based on count

Re: SQL help: delete recs based on count

From: Sriram Kumar <k.sriramkumar_at_gmail.com>
Date: 2005-12-30 00:47:29
Message-id: c515faee0512291547q3b7293a8n4482e4adb0b1487@mail.gmail.com


Hi Barbara,

Based on my understanding, I have written a piece of code that might help. Pls test and use it if it meets your requirement.

Best Regards

Sriram Kumar

===========Code========================

declare

  /* Fetch Personal Id's Having more than 100 Records */    cursor cur_delete is
   select a.personal_id
   from LAST_ACCESSED_LIST a
   group by a.personal_id
   having count(*) >=100 ;

   lr_rowid rowid;

  begin

  for j in cur_delete
   loop

       /* Select rowid of the 99 record. Any record

* that has rowid greater than this is eligible
* for deletion */
select max(rowid_99) into lr_rowid from ( select rowid rowid_99 from LAST_ACCESSED_LIST b where b.personal_id=j.personal_id and rownum 99th record * Need Be rewrite this as bulk delete with limit 5000 */ delete from LAST_ACCESSED_LIST c where c.personal_id=j.personal_id and c.rowid > lr_rowid; dbms_output.put_line('Deleted ' || sql%rowcount);

   end loop;

  end ;

=============end of code=========================



On 12/29/05, Barbara Baker wrote:

>
> OpenVMS 7.3-1; Oracle 9.2.0.4
>
> The table LAST_ACCESSED_LIST has 3 columns
>
>   Name                                      Null?    Type
> ----------------------------------------- -------- ---------
>  PERSONNEL_ID                              NOT NULL NUMBER(9)
>  COMPANY_ID                                NOT NULL NUMBER(9)
>  ACCESS_DATE                                        DATE
>
> I want to (actually, developer wants to) remove all the records from the
> table for a given user (a specific personnel_id)
> where user has more than 100 records.  A user currently may have as few as
> 1 record or as many as 1000.
>
> I cannot come up with syntax.  This is definitely NOT what I want
>
> select personnel_id, count(*) from last_accessed_list
>             group by personnel_id having count(*) < 20
>
> but I cannot figure out how to count/gather the total number of records
> for a specific personnel_id.
> There is a primary key on personel_id + company_id, if this helps.
>
> Any help greatly appreciated.  Thanks!
>
> Barb
>
Received on Fri Dec 30 2005 - 00:47:29 CST

Original text of this message

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