Query to delete duplicate records from table using sql

Query to delete duplicate records from table using sql

 select * from test where exists (select * from test as t where test.id=t.id having count(*)>1 and min(t.id) !=test.id.);  

How to Remove Duplicates from a Single Column SQL Table

To Remove Duplicates from a Single Column Table in sql you can use temparary table concept and can try this way

 lock tables Table1 write;  
 drop temporary table if exists TestTmp;  
 begin work;  
 create temporary table TestTmp select distinct * from Table1;  
 delete from Table1;  
 insert into Table1 select * from TestTmp;  
 unlock tables;  
 commit;  

delete all the duplicate rows in the table of SQL database

If the table is not having an unique column you can use the below:

 DELETE  
 FROM _Table1  
 WHERE _Table1.ID IN  
 -- List 1 - all rows that have duplicates  
 (SELECT F.ID  
 FROM _Table1 AS F  
 WHERE Exists (SELECT Field1, Field2, Count(ID)  
 FROM _Table1  
 WHERE _Table1.Field1 = F.Field1 AND _Table1.Field2 = F.Field2  
 GROUP BY _Table1.Field1, _Table1.Field2  
 HAVING Count(_Table1.ID) > 1))  
 AND _Table1.ID NOT IN  
 -- List 2 - one row from each set of duplicate  
 (SELECT Min(ID) FROM _Table1 AS F  
 WHERE Exists (SELECT Field1, Field2, Count(ID) FROM _Table1  
 WHERE _Table1.Field1 = F.Field1 AND _Table1.Field2 = F.Field2  
 GROUP BY _Table1.Field1, _Table1.Field2  
 HAVING Count(_Table1.ID) > 1)  
 GROUP BY Field1, Field2);