Oracle PL/SQL - How to delete multiple duplicate records in the SQL table?

I have table name TABLE1 that contains duplicate records as shown below:

ID  TYPE  AMOUNT NUMBER      DATE
--- ----  ------ ------ ---------
1    AAA   10.00 AAA123 22-JUN-11 
2    AAA    2.00 AAA123 22-JUN-11 
3    AAA   10.00 AAA123 22-JUN-11 
4    AAA    2.00 AAA123 22-JUN-11 
5    AAA   10.00 AAA123 22-JUN-11 
6    AAA    2.00 AAA123 22-JUN-11 
7    AAA   10.00 AAA123 22-JUN-11 
8    AAA    2.00 AAA123 22-JUN-11 
...  ...     ...    ...       ...
100  AAA   10.00 AAA123 22-JUN-11
101  AAA    2.00 AAA123 22-JUN-11

In this case, I would like to remove ALL dup开发者_如何学Pythonlicate combination rows, except two using SQL or/via PL/SQL where AMOUNT (10.00 and 2.00). In addition, the duplicate recordes containing different amounts could be more than two such as shown below:

ID  TYPE  AMOUNT NUMBER      DATE
--- ----  ------ ------ ---------
1    AAA   10.00 AAA123 22-JUN-11 
2    AAA    2.00 AAA123 22-JUN-11 
3    AAA   15.00 AAA123 22-JUN-11 
4    AAA   25.50 AAA123 22-JUN-11 
5    AAA   10.00 AAA123 22-JUN-11 
6    AAA    2.00 AAA123 22-JUN-11 
7    AAA   15.00 AAA123 22-JUN-11 
8    AAA   25.50 AAA123 22-JUN-11 
...

In the above example, I need to delete only 4 out of 8 records, where AMOUNT should remain 4 records (10.00, 2.00, 15.00 and 25.50). In other words, I have multiple groups of duplicates in one table (2 records for one, 4 for another, etc) - multiple rows where more than one exists.

Try this:

DELETE 
    FROM  TABLE1
    WHERE ROWID IN 
    (
        SELECT ROW_ID_VAL
          FROM 
            (
                SELECT a.*, 
                       RANK() OVER(PARTITION BY AMOUNT ORDER BY ID DESC) RN, ROWID row_id_val
                  FROM TABLE1 a
            )
            WHERE rn <> 1
    )

The DATE column is the only one that needs more info; otherwise use:

DELETE FROM YOUR_TABLE
 WHERE EXISTS (SELECT NULL
                 FROM YOUR_TABLE t
                WHERE t.type = YOUR_TABLE.type
                  AND t.amount = YOUR_TABLE.amount
                  AND t.number = YOUR_TABLE.number
                  AND t.date = YOUR_TABLE.date
             GROUP BY t.type, t.amount, t.number, t.date
               HAVING MIN(t.id) != YOUR_TABLE.id)

The YOUR_TABLE.column references refer to the outer YOUR_TABLE, the table the deletion is to be performed on. This gives it a correlated subquery effect, but EXISTS doesn't quite function that way.

One method you may follow is as the following:

Each row has a unique (row id). You can identify the duplicated rows of course and then delete the duplicated rows based on (row id). Just type the following SELECT statement to reveal the duplicated rows id:

SELECT rowid from table_name;

DECLARE

BEGIN

  for rec_ in (
                 SELECT type, amount, number, date , count(1) record_count
                    FROM table 1
                  GROUP BY type, amount, number, date
                 HAVING count(1) > 1) loop

      counter_ := 0;

      for rec2_ in ( select * from table1 where rec_.type = type
                                            and rec_.amount = amount
                                            and rec_.number = number
                                            and rec_.date = date) loop

            counter_ := counter_ + 1;            
            exit when counter_ = rec_.record_count;
            delete from table1 where id = rec2_.id;
       End loop;

  end loop;
END;