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

Question

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 duplicate 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.

The community frowns on tagging PLSQL when the answer really only uses SQL. For what version of Oracle? — OMG Ponies
– OMG Ponies, Commented Jun 23, 2011 at 14:54

Chandu · Accepted Answer · 2011-06-23 14:50:52Z

3

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
    )

answered Jun 23, 2011 at 14:50

Chandu

83.2k19 gold badges136 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

OMG Ponies Over a year ago

ROW_NUMBER might be a better choice in this situation?

Chandu Over a year ago

@OMG Pointes: I presume Id field is unique and RANK shuold still be fine as I am ordering the data in a window using ID.

OMG Ponies · Accepted Answer · 2011-06-23 14:50:17Z

2

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.

answered Jun 23, 2011 at 14:50

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Comments

Lucas Zamboulis · Accepted Answer · 2014-09-01 18:49:48Z

1

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;

edited Sep 1, 2014 at 18:49

Lucas Zamboulis

2,5515 gold badges24 silver badges28 bronze badges

answered Sep 1, 2014 at 17:57

Mussa

3121 silver badge10 bronze badges

Comments

Calipso · Accepted Answer · 2014-09-01 19:08:21Z

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;

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related