Delete all duplicate records from Oracle table except oldest

7
votes

I have 2 tables, one parent TableA and one child TableB. TableB has 1 or more records with a parent record in TableA. I need to delete all records from TableB except the earliest date i.e. all duplicates in TableB. I don't think TableA needs to be involved in the statement but I'm including it just for reference.

TableA 
_______ 
SecID, SecName 
1,     Sec1
2,     Sec2
3,     Sec3
4,     Sec4

TableB
_________
IncID, SecID, PayDate 
16,    1,     11/03/2011
17,    1,     11/04/2011
18,    2,     10/01/2011
19,    3,     01/06/2011
20,    3,     01/09/2011
21,    3,     01/12/2011
22,    4,     10/06/2011

So in TableB above I need to delete records 17, 20, and 21 leaving one record for each SecID. So far I have below but for some reason it's including the earliest record which I want to keep:

delete from TableB where PayDate not in (
  select min(PayDate)from TableB
  having ( count(PayDate) > 1 )
)
3
sqloracle

3 Answers

20
votes

you can use ROWID and analytics:

SQL> DELETE FROM tableB
  2   WHERE ROWID NOT IN
  3           (SELECT first_value(ROWID)over(PARTITION BY secID ORDER BY paydate)
  4              FROM tableB);

3 rows deleted

SQL> select * from tableB;

     INCID      SECID PAYDATE
---------- ---------- -----------
        16          1 11/03/2011
        18          2 10/01/2011
        19          3 01/06/2011
        22          4 10/06/2011

You could also use a more conventional semi-join:

SQL> DELETE FROM tableB b_out
  2   WHERE EXISTS (SELECT NULL
  3                   FROM tableB b_in
  4                  WHERE b_in.secID = b_out.secID
  5                    AND b_in.paydate < b_out.paydate);

3 rows deleted
7
votes

TABLE 

 ID    RefCode   Code_Desc
 122   B122      The Notebook
 122   B122      The Notebook
 122   B122      The Notebook
 123   B123      A Walk to Remember
 123   B123      A Walk to Remember
 123   B123      A Walk to Remember
 123   B123      A Walk to Remember

To delete All duplicate records except one

delete from TABLE a where rowid<(select max(rowid) from TABLE b where a.ID = b.ID)

To delete Specific duplicate records except one

delete from TABLE a where rowid<(select max(rowid) from TABLE b where a.ID = b.ID and a.ID = 122)

0
votes
delete from your_table a
where a.rowid not in 
(
  select max(b.rowid) from your_table b
  group by b.col1,b.col2....b.coln
)

This will get all unique rowids and except these rowid-s the sql will delete all rows.