How to delete duplicate data from a table in a database __ database

There is an automatic growth column C1 in a table, at the same time, there are two health columns pointing to the other two primary key c2,c3, and suddenly one day I want to C2 and C3 together as a unique key, this time must remove the C2 and C3 of the duplicate key (because C1 can not repeat), this is the topic to be discussed today, How to remove duplicate keys in this case.

In the online query about the deletion of duplicate key practices, many of the original table with a copy of the distinct query form to the temporary table, and then delete the original table, and finally copy the temporary table to the original table. The approximate code is as follows:

SELECT DISTINCT * into #temp from table1;

Delete table1;

INSERT INTO table1 select * from #temp;

Drop #temp;

This method is also an effective way to remove duplicate rows, but for the disadvantage is that you cannot delete partially duplicated rows, and if the table has triggers, it may cause some other problems (see the originating code), and how can you effectively delete duplicate rows?

Here my idea is to be able to find duplicate rows of the primary key, and then remove the condition. In this way, I must first query for duplicate columns and import the query results into the temporary table #temp:

Select C2,c3 into #temp from table group by C2,C3 have count (*) = 2;

Then use the original table joint query to get the primary key information, in order to remove the convenience behind, I cited an automatic growth column and then use C2 or C3 to sort, is also a key to solve the problem here, the query results into the #temp2;

Select Identity (int,1,1) as id,a.c2,a.c3 into #temp2 from table1 as a inner join #temp b on a.c1 = b.c1 ORDER by A.C2

At this point, the #temp2 table already retains all of the duplicate data, but we want to get rid of a row in the duplicate row, so we can write:

Delete #temp2 where id%2=1 (odd rows)

or delete #temp2 where id%2=0 (even rows)

At this point, the table #temp2 retains only the duplicate data rows, and finally you can delete:

Delete table1 where C1 in (select C1 from #temp2)

Done. The duplicate data is gone.

Of course, this is just a list of duplicates, and if you repeat multiple rows, you can modify the count (*) =n, and modify the delete #temp2的条件子句 accordingly.

In fact, the coding may be more complex than this, I also just simplified the problem, if you have different opinions, welcome to correct the ^_^