Saturday, August 15, 2009

How To: Delete Duplicate Rows from a Table


Problem: You have a table (table1) & values as above, and it contains the duplicate records as shown, how will you delete the duplicates?

Solution: Use following query, the records shown in the below image will be deleted
delete from table1 where empid not in
(select max(empid) from table1 group by empName,empEmail,empCity);

2 comments:

Ram said...

hi kiran,
1. the scenario is wrong
we find the employee id as different this is unique rows so not duplicate rows
2. review the query

Kiran said...

Hi Ram,

If you dont have primary key column in your table, alter the table define a primary key column, delete it duplicate and again alter the table and remove the primary key column.

Thanks
:)