Remove duplicates based on two fields

Question

I have a table with 14 columns which can be referred to as column_1 - column_14. I need a table that is unique on a combination of two fields (i.e. column_1 and column_2). I cannot have any instances in this table where there are multiple rows containing the same information in column_1 and 2. To give a clear understanding of what I mean, I referenced this post to identify the duplicates I speak of.

I have referenced this post

Now, I need to learn how to delete these rows from my table, so I am left with completely unique rows based on columns 1 & 2.

Thank you

You need a `group by` clause. – juergen d Oct 29 '14 at 20:12 — juergen d, Oct 29 '14 at 20:12

geoandri · Accepted Answer · 2014-10-29T21:04:51.203

6

In order to find you duplicates you can use the following query

SELECT * FROM your_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM your_table
GROUP BY column1, column2); //those are the columns that define which row is unique

In order to delete the duplicates

  DELETE FROM your_table
 WHERE rowid not in
 (SELECT MIN(rowid)
 FROM your_table
 GROUP BY column1, column2); //those are the columns that define  row is unique

edited Oct 29 '14 at 21:04

answered Oct 29 '14 at 20:20

geoandri

2,360
2
15
28

What do I substitute in for id? Oracle told me, invalid identifier. – Dillon Tagle Oct 29 '14 at 20:46
The primary key of your table or a unique identifier for each row – geoandri Oct 29 '14 at 20:50
There is no unique identifier. Column_1 is the most unique identifier I have, thus the issue seeing as there are duplicates – Dillon Tagle Oct 29 '14 at 20:54
1

Then use rowid instead of id.Check Gordon Linoff 's answer – geoandri Oct 29 '14 at 20:57

Gordon Linoff · Answer 2 · 2014-10-30T02:54:04.477

2

Oracle has a rowid pseudo-column that can be used exactly for this purpose. This means that you can do what you want even if your data has no unique ids (which is quite common in Oracle databases because setting up an auto-incrementing primary key is painful prior to Oracle 12).

The code looks like:

DELETE FROM table t
    WHERE rowid > (SELECT min(rowid) FROM table t2 WHERE t2.col1 = t.col1 and t2.col2 = t.col2);

Note that min(rowid) is an arbitrary row. It is not necessarily the first row inserted with the two values.

edited Oct 30 '14 at 02:54

answered Oct 29 '14 at 20:24

Gordon Linoff

1,242,037
58
646
786

No table is ever referenced as just t... only a t2. Hence this would not run for me – Dillon Tagle Oct 29 '14 at 20:55
@DillonTagle . . . I left the column alias out of the `from` statement by mistake. – Gordon Linoff Oct 30 '14 at 02:54

score -1 · Answer 3 · answered Oct 29 '14 at 20:35

-1

WITH Temp (column_1, column_2, duplicateRecCount)
AS
(
SELECT column_1, column_2, ROW_NUMBER() OVER(PARTITION by column_1, column_2 ORDER BY column_1, column_2) 
AS duplicateRecCount
FROM Your_Table
)
--Now Delete Duplicate Records
DELETE FROM Temp
WHERE duplicateRecCount > 1

answered Oct 29 '14 at 20:35

Musheer Khalid

3
3

I thought the same, but it's not valid in Oracle. Also, if you highlight all your code and hit the `{}` button it will format it as code. – Hart CO Oct 29 '14 at 20:37

Remove duplicates based on two fields

3 Answers3