How Can I Remove Duplicate Rows from an Oracle Table While Keeping One Unique Row?

Removing Duplicate Rows in Oracle Tables: A Practical Approach

During Oracle table population, duplicate rows can unintentionally arise, preventing the creation of a primary key. This article details a method to efficiently remove these duplicates while retaining a single unique row.

Solution: Utilizing the rowid Pseudocolumn

Oracle's rowid pseudocolumn provides a unique identifier for each row. We leverage this to identify and delete redundant entries.

The following SQL statement uses rowid to achieve this:

<code class="language-sql">DELETE FROM your_table
WHERE rowid NOT IN
(SELECT MIN(rowid)
FROM your_table
GROUP BY column1, column2, column3);</code>

This query deletes rows whose rowid doesn't correspond to the minimum rowid within each group of duplicates. The GROUP BY clause defines the grouping based on the specified columns (column1, column2, column3). Only one row per unique combination of these column values will remain.

For precise duplicate removal, consider including all columns in the GROUP BY clause. Adjust the column names to match your specific table structure.

This approach ensures only unique records are retained, paving the way for primary key creation and other database operations.

The above is the detailed content of How Can I Remove Duplicate Rows from an Oracle Table While Keeping One Unique Row?. For more information, please follow other related articles on the PHP Chinese website!