How can I eliminate duplicate values in Oracle and keep only one of them?

2 years ago
William Carter
1 minute

To remove duplicate values in an Oracle database table and only keep one duplicate value, you can use the following method:

Option 1: Utilize ROWID.

DELETE FROM your_table
WHERE ROWID NOT IN (
    SELECT MAX(ROWID)
    FROM your_table
    GROUP BY column1, column2, ...
);

Replace your_table with the name of your table, and replace column1, column2, … with the column names you want to deduplicate.

Option two: Utilize the ROW_NUMBER() function.

DELETE FROM your_table
WHERE ROWID NOT IN (
    SELECT ROWID
    FROM (
        SELECT ROW_NUMBER() OVER (PARTITION BY column1, column2, ... ORDER BY column1) AS rn
        FROM your_table
    )
    WHERE rn > 1
);

You also need to replace your_table with the name of your table, and replace column1, column2, … with the names of the columns you want to use for deduplication.

bannerAds