How to Delete duplicate Rows from Table
In the below table studId 1,6 and 9 is repeated which should be deleted.
studId | studentName | age |
---|---|---|
1 | Mugil | 35 |
2 | Vinu | 36 |
3 | Viju | 42 |
4 | Mani | 35 |
5 | Madhu | 36 |
6 | Mugil | 35 |
7 | Venu | 37 |
8 | Banu | 34 |
9 | Mugil | 35 |
Below query wont work on MySQL but the format doesn’t change. When you take Max only last occurrence of row would be taken and others would be excluded.
DELETE FROM tblStudents TS WHERE TS.studId NOT IN (SELECT MAX(TSS.studId) FROM tblStudents TSS GROUP BY TSS.studentName, TSS.age)c
The same could be done using MIN function.
SELECT TS.* FROM tblStudents TS WHERE TS.studId NOT IN (SELECT MIN(TSS.studId) FROM tblStudents TSS GROUP BY TSS.studentName, TSS.age)
Output
studId | studentName | age |
---|---|---|
6 | Mugil | 35 |
9 | Mugil | 35 |