SQL Automatically Delete one of two duplicates

Question

I have a query which finds duplicate IDs

 SELECT uniqueID2, count(uniqueID2) as 'count'
                                  FROM gpDetailAfterMultiplier
                                   group by uniqueID2
                                  having count(uniqueID2) > 1

this produces an output something like:

uniqueID2     count
111111111       2
111111112       2
111111113       2
111111114       2

How do I automatically delete one of the two duplicates?

I can do this one at a time by doing

DELETE top(1) from gpDetailAfterMultiplier where UniqueID2 = '111111111'

is there any way to do this so that it automatially 'loops' through each result and deletes one of the two duplicates for each unique id?

Do you have another column that is really unique - like an `id`? — juergen d, Apr 02 '14 at 16:38
@juergend no unfortunetly not :/ this is why I need to get rid of the duplicates — neeko, Apr 02 '14 at 16:38
Also, are there only 2 records for each ID or are more possible? — shree.pat18, Apr 02 '14 at 16:52
Add a primary key that is autonumber, that will give you something that is unique. — Lasse V. Karlsen, Apr 02 '14 at 16:53
The "unique" column doesn't even need to be universally unique; just unique between the records with the same id. That way you can join on `uniqueID2` and `Max(some_other_column)` and only get one record. Something like an insert date — Brad, Apr 02 '14 at 16:54

score 1 · Answer 1 · edited Apr 02 '14 at 16:57

1

Try this:

WITH CTE AS(
   SELECT *,
       RN = ROW_NUMBER()OVER(PARTITION BY uniqueID2 ORDER BY uniqueID2)
   FROM gpDetailAfterMultiplier
)
DELETE FROM CTE WHERE RN > 1

It will delete all duplicates from the table.

See result in Fiddle (Used SELECT query in fiddle to see which records are going to be deleted).

edited Apr 02 '14 at 16:57

juergen d

195,137
36
275
343

answered Apr 02 '14 at 16:39

Raging Bull

18,113
13
47
53

Care to adapt your example to the OP's question? – juergen d Apr 02 '14 at 16:44
thanks for your reply, will this only delete one of the two duplicates or both? – neeko Apr 02 '14 at 16:50
@neeko: It will delete all others except one (See the result in fiddle). – Raging Bull Apr 02 '14 at 16:51
@neeko This answer works so I have deleted mine. – shree.pat18 Apr 02 '14 at 16:58
Why did I get a downvote for the right answer? – Raging Bull Apr 02 '14 at 16:59
Updated fiddle to show results of delete. http://sqlfiddle.com/#!3/a8e93/1 – Ron Smith Apr 02 '14 at 17:03

SQL Automatically Delete one of two duplicates

1 Answers1