SQL query to find and delete duplicate records

Best database practice is to have a unique constraint such as a primary key on tables to prevent duplicate records, but there are some situations in which we may found ourselves working on a dataset with duplicate records and we may also need to remove or delete those records when needed.

There are many ways using which we can find duplicate records, the best approach is to find the duplicate records using ROWID.

Below is the SQL query to find and delete the duplicate records.

SQL Query to find duplicate records.

SELECT * FROM EMP a WHERE ROWID!=(SELECT MAX(ROWID) FROM EMP B where a.empno=b.empno);

Output:

SQL query to delete the duplicate records.

DELETE FROM EMP a WHERE ROWID!=(SELECT MAX(ROWID) FROM EMP B where a.empno=b.empno);

Do post in comment sections if you want any more such queries.