delete duplicate records from sql

Question

In my table I have so many duplicate records

SELECT ENROLMENT_NO_DATE,  COUNT(ENROLMENT_NO_DATE) AS NumOccurrences
FROM Import_Master GROUP BY ENROLMENT_NO_DATE HAVING ( COUNT(ENROLMENT_NO_DATE) > 1 )

I need to remove duplicate record if it is occur second time... Need to keep first or any of one record. How can I do that?

http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql — JDGuide, Aug 21 '12 at 11:42
@João No one... but need ENROLMENT_NO_DATE as unique in table — Lajja Thaker, Aug 21 '12 at 11:44
There are many posts related to this topic.Please go through these links.http://stackoverflow.com/questions/1286843/how-to-keep-only-one-row-of-a-table-removing-duplicate-rows — JDGuide, Aug 21 '12 at 11:44

score 4 · Accepted Answer · answered Aug 21 '12 at 11:55

4

You can use CTE to perform this task:

;with cte as
(
  select ENROLMENT_NO_DATE, 
    row_number() over(partition by ENROLMENT_NO_DATE order by ENROLMENT_NO_DATE) rn
  from Import_Master
) 
delete from cte where rn > 1

See SQL Fddle with Demo

answered Aug 21 '12 at 11:55

Taryn

242,637
56
362
405

@bluefeet I've always thought of CTEs as temp tables, it seems wrongly, so I'm surprised that deleting from CTE, deletes from source. – saj Aug 21 '12 at 12:02

score 1 · Answer 2 · answered Aug 21 '12 at 11:54

One method could be to create a secondary, temporary table

CREATE TABLE Import_Master_Deduped AS SELECT * FROM Import_Master WHERE FALSE;

This will create an empty table with identical structure to Import_Master. Now impose uniqueness on the new table with an index:

CREATE UNIQUE INDEX Import_Master_Ndx ON Import_Master_Deduped(ENROLMENT_NO_DATE);

Finally copy the table with duplicated records inside with INSERT IGNORE, so that duplicated records will not get inserted:

INSERT IGNORE INTO Import_Master_Deduped SELECT * FROM Import_Master;

At this point, after checking everything is OK, you can rename the two tables swapping their names (this will lose any old indexes), or TRUNCATE the Import_Master table and copy back the deduped records from the new table into the old.

In the second case, recreate the UNIQUE constraint on the old table to avoid further duplicates; in the first, recreate any old indexes on the new table.

Finally, you remove the table you don't need anymore.

delete duplicate records from sql

2 Answers2