0

Possible Duplicate:
Removing duplicate rows from table in Oracle

Here's an example of what I currently have:

ID        Name1    Name2
ABC123    Bob      Jones
ABC123    Bob      Smith
ABC123    Bob      Hammond
DEF234    Tim      Whatever

I'd like the table above to look like this:

ID        Name1    Name2
ABC123    Bob      Jones
DEF234    Tim      Whatever

I'd like to delete all rows where the ID field is shared, I don't care which ones are kept or deleted, just that the number of total rows is equal to the number of unique IDs.

Community
  • 1
  • 1

3 Answers3

3
DELETE FROM YOURTABLE Y WHERE ROWID > (SELECT min(rowid) FROM YOURTABLE X
WHERE X.ID = Y.ID)
bonsvr
  • 2,262
  • 5
  • 22
  • 33
  • What exactly do the X and Y represent? –  Aug 24 '12 at 23:54
  • They are table aliases. For example once you write YOUTABLE X, you can use X instead of YOURTABLE in that query. So, they represent YOURTABLE, or whatever the name of your table.You dont have to use X or Y, you can use any characters or words (except reserved words in Oracle). – bonsvr Aug 25 '12 at 01:04
  • I see now, that did the trick. Thanks bonsvr! –  Aug 25 '12 at 11:35
  • 1
    Can you mark this as correct solr? I almost just tried answering this myself. – Reimius Aug 25 '12 at 12:47
0

If you don't have global unique ID for whatever reason, then something like this should work:

DELETE FROM Table WHERE CONCAT(ID,Name1,Name2) NOT IN ( 
       SELECT MIN(CONCAT(ID,Name1,Name2)) FROM Table GROUP BY ID)
dfb
  • 13,133
  • 2
  • 31
  • 52
0
 DELETE FROM your_table
      WHERE ROWID IN (
               SELECT rid
                  FROM (SELECT ROWID rid,
                           ROW_NUMBER () OVER (PARTITION BY ID ORDER BY ROWID) rn
                         FROM your_table)
                WHERE rn <> 1);
Gaurav Soni
  • 6,278
  • 9
  • 52
  • 72