CODEWITHSUNDEEP
sqloracle-database
Dillon Tagle
Dillon Tagle

Reputation: 63

Remove duplicates based on two fields

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

Upvotes: 1

Views: 6155

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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.

Upvotes: 2

geoandri
geoandri

Reputation: 2428

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

Upvotes: 9

Musheer Khalid
Musheer Khalid

Reputation: 3

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

Upvotes: -1

Related Questions