How Do I Delete Duplicate Rows in Oracle Tables While Preserving Data Integrity?

Efficiently Removing Duplicate Rows from Oracle Tables

Data duplication in Oracle tables can arise from various sources, often hindering the creation of primary keys and compromising data integrity. This article provides a straightforward method to eliminate duplicate rows, preserving a single unique entry for each record.

The Solution:

Leveraging the rowid pseudocolumn, we can effectively identify and delete duplicate rows. The following SQL statement achieves 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>

Explanation:

• your_table: Replace this with the name of your table containing the duplicate rows.
• rowid: This unique identifier for each row is crucial for this process.
• The subquery (SELECT MIN(rowid) FROM your_table GROUP BY column1, column2, column3) identifies the minimum rowid for each unique combination of column1, column2, and column3. This ensures that only one row per unique data set is retained.
• column1, column2, and column3: These represent the columns forming the unique key for each record. Adjust these to match the columns defining uniqueness in your table. You can add more columns as needed.

Executing this query will pinpoint and remove duplicate rows, leaving only one distinct record for each specified key combination. This enables the creation of primary keys based on these columns without data integrity conflicts.

The above is the detailed content of How Do I Delete Duplicate Rows in Oracle Tables While Preserving Data Integrity?. For more information, please follow other related articles on the PHP Chinese website!