CODEWITHSUNDEEP
phpmysqlsql
shahalpk
shahalpk

Reputation: 3452

Find duplicate entries in a table

I have a messcuts table with the following structure.
id, student_rollno, date.

The problem is there are some records duplicated ie. two records with same student_rollno in the same date. How do I remove them? Eg: 

SELECT * 
FROM  `messcuts` 
WHERE student_rollno =  'b070226'

|id |student_rollno|date
|259|B070226|2011-08-06
|260|B070226|2011-08-07
|1485|B070226|2011-08-12
|1486|B070226|2011-08-13
|1487|B070226|2011-08-14
|1488|B070226|2011-08-15
|2372|B070226|2011-08-27
|2369|B070226|2011-08-24
|2368|B070226|2011-08-23
|2371|B070226|2011-08-26
|2374|B070226|2011-08-29
|2373|B070226|2011-08-28
|2370|B070226|2011-08-25
|2367|B070226|2011-08-22
|2375|B070226|2011-08-30
|2376|B070226|2011-08-31
|2938|b070226|2011-08-06

See on 2011-08-06 there are two records.

Upvotes: 0

Views: 440

Answers (2)

Kevin Burton
Kevin Burton

Reputation: 11936

This will show the duplicates

SELECT * 
FROM messcuts
WHERE (student_rollno,date) IN
    (
    SELECT student_rollno,date
    FROM messcuts
    GROUP BY student_rollno,date
    HAVING count(*)>1
    )

and to delete:

CREATE VIEW dups AS
SELECT MAX(id) as id
    FROM messcuts
    GROUP BY student_rollno,date
    HAVING count(*)>1

you will need to run this several times to get rid of all duplicates

DELETE FROM t2 
WHERE id IN (SELECT  id FROM dups)

once you have removed the duplicates It may be a good idea to add a unique constraint to stop furthur problems.

ALTER TABLE messcuts ADD UNIQUE std_dte (student_rollno,date)

Upvotes: -1

roselan
roselan

Reputation: 3775

select student_rollno, date 
from messcuts 
group by student_rollno, date 
having count(*) > 1

and to delete:

delete from messcuts d where d.id in (
    select max(s.id) 
    from messcuts as s 
    group by s.student_rollno, s.date 
    having count(*) > 1)

if not working in mysql:

delete from messcuts 
using messcuts, messcuts as v_messcuts
where messcuts.id <> v_messcuts.id
and messcuts.student_rollno = v_messcuts.student_rollno
and messcuts.date = v_messcuts.date

Upvotes: 7

Related Questions