How to delete duplicate records from a table in oracle?

@domenico 

To delete duplicate records from a table in Oracle, you can use a combination of SQL queries and the ROWID pseudocolumn. Here's a step-by-step guide on how to do it:

1. Identify the duplicate records: You can identify duplicate records by running a query that groups by the fields you suspect have duplicates and counts the number of occurrences. For example:

1
2
3
4

SELECT column1, column2, column3, COUNT(*)
FROM your_table
GROUP BY column1, column2, column3
HAVING COUNT(*) > 1;

1. Use the ROWID pseudocolumn to delete duplicate records: Once you have identified the duplicate records, you can use the ROWID pseudocolumn to delete them. The ROWID is a unique identifier for each row in a table. You can use it to select and delete specific rows.

1
2
3
4
5

DELETE FROM your_table
WHERE rowid not in 
(SELECT MAX(rowid) 
FROM your_table
GROUP BY column1, column2, column3);

This query deletes all rows except for the one with the highest ROWID for each group of duplicate records. Make sure to replace your_table, column1, column2, and column3 with the actual table name and column names you are working with.

1. Commit the changes: After running the DELETE query, remember to commit the changes to make them permanent:

1

COMMIT;

By following these steps, you can efficiently delete duplicate records from a table in Oracle.