Re: urgent

From: Michael Barger <mrbarger_at_onramp.net>
Date: Wed, 18 Oct 2000 15:13:05 -0500
Message-Id: <10653.119709@fatcity.com>

This could best be done by a stored proc... Here is an example:

set serveroutput on
set echo on;
set verify on;
set feedback on;
set termout on;

DECLARE

     V_ACCT_NO                   VARCHAR2(21);
     V_COUNT                     NUMBER(10) := 0;
     V_TOT_COUNT                 NUMBER(10) := 0;


     /* This will find duplicate records based on the select fields */
     CURSOR dupes IS
         SELECT DISTINCT wnaccountnumber, count(*)
           FROM sw_customer WHERE swcreatedby = 'arbor'
       GROUP BY wnaccountnumber
         HAVING COUNT(*) > 1
         ;

BEGIN      OPEN dupes;

     LOOP
         FETCH dupes
          INTO V_ACCT_NO, V_COUNT
         ;

         EXIT WHEN dupes%NOTFOUND OR dupes%NOTFOUND IS NULL;

         DELETE from sw_customer
          WHERE wnaccountnumber  = V_ACCT_NO
            AND rownum           < V_COUNT
         ;

         V_TOT_COUNT := V_TOT_COUNT + V_COUNT;

     END LOOP;

     CLOSE dupes;
     commit;

     INSERT INTO
         msg VALUES(to_char(V_TOT_COUNT) || ' dupes')
     ;

      commit;

END;
/
exit;

At 12:06 PM 10/18/00 -0800, Swapna_P wrote:

>how to delete the duplicate records from a table............
>except for one field all the rest of the fields are the same,so want to
Received on Wed Oct 18 2000 - 15:13:05 CDT