How to delete duplicate records from a table in oracle?

Member

by domenico , in category: MySQL , 2 months ago

How to delete duplicate records from a table in oracle?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

Member

by aubrey , 2 months ago

@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.