Remove duplicate records from Table in Oracle

Suppose by mistake, many duplicate records have been entered in the database and you need to keep only distinct records.

You can use the following query to achieve this.

First we select only the distinct records by using MIN(ROWID).

Later we delete all the remaining duplicate records which are not present in the distinct records fetched earlier.

DELETE FROM GTB_CUST_ACCOUNT
WHERE ROWID NOT IN
(SELECT MIN(ROWID)
FROM GTB_CUST_ACCOUNT
GROUP BY ACCOUNT_NO||'~'||TRANSIT_NO);