CODEWITHSUNDEEP
sqlsql-server
Lajja Thaker
Lajja Thaker

Reputation: 2041

delete duplicate records from sql

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?

Upvotes: 1

Views: 333

Answers (2)

Taryn
Taryn

Reputation: 247620

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

Upvotes: 4

LSerni
LSerni

Reputation: 57378

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.

Upvotes: 1

Related Questions