Oracle duplicate record deletion-CodePudding

I have a table with main fields

ArticleNo,Barcode,UnitOfMeasure etc

How i can remove the duplicate records from the table if articleno and barcode and unitofmeasure are same

Trying to remove the duplicate records from oracle table

CodePudding user response：

Usual way of doing it is

delete from your_table a
where a.rowid > (select min(b.rowid)
                 from your_table b
                 where a.articleno = b.articleno
                   and a.barcode = b.barcode
                   and a.unitofmeasure = b.unitofmeasure);

CodePudding user response：

I usally do this by filtering the MIN or MAX rowids for the given combinations:

DELETE 
  FROM some_table
 WHERE ROWID NOT IN (
                     SELECT rid
                       FROM (
                             SELECT MIN(rowid) as rid, ArticleNo, Barcode, UnitOfMeasure
                               FROM some_table 
                              GROUP BY ArticleNo, Barcode, UnitOfMeasure
                            )
                    );