How can I remove duplicates from an Oracle table while keeping the most recent record?

Removing duplicate rows from an Oracle table while retaining the most recent record can be achieved using SQL. The general strategy involves using window functions to identify and then delete the duplicate rows, based on a timestamp or another column indicating record recency.

Here's a detailed breakdown of how to accomplish this:

1. Identifying Duplicates with ROW_NUMBER():

- We use the ROW_NUMBER() window function to assign a unique rank to each record within groups of duplicates. The ranking is ordered by the 'recency' column in descending order, ensuring that the most recent record gets rank 1.

- For example, consider a table named my_table with columns like id, value, updated_at, where updated_at is the timestamp column. You would partition by the column that identifies duplicates (e.g., value) and order by updated_at.

2. The SQL Query:

DELETE FROM my_table
WHERE rowid IN (
  SELECT rowid
  FROM (
    SELECT rowid, ROW_NUMBER() OVER (PARTITION BY value ORDER BY updated_at DESC) as rn
    FROM my_table
  )
  WHERE rn > 1
);

- This SQL query does the following:

- The inner subquery assigns a row number (rn) to each record, partitioned by the value and ordered by updated_at in descending order.

- The outer subquery selects the rowid for all rows with rn > 1, which are the duplicate rows that are not the most recent.

- Finally, the DELETE statement removes these identified duplicate rows from my_table.

3. Important Considerations:

- Replace my_table, value, and updated_at with your actual table and column names.

- If you have multiple columns that determine duplicate records, include them all in the PARTITION BY clause.

- Make sure the column used for ordering (updated_at in this example) accurately represents the 'recency' of the records.

- It is wise to run the inner subquery as a SELECT statement first to verify the results before executing the DELETE statement.

4. Example with multiple columns:

If duplicates are based on multiple columns, such as value1 and value2:

DELETE FROM my_table
WHERE rowid IN (
  SELECT rowid
  FROM (
    SELECT rowid, ROW_NUMBER() OVER (PARTITION BY value1, value2 ORDER BY updated_at DESC) as rn
  FROM my_table
  )
  WHERE rn > 1
);

This method efficiently removes duplicates while preserving the most recent entries in your Oracle table.

Learn more about this