• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

SQL Server 2008 search for and remove dulpicates

I have a table called plant.  A column called certificate.

Is there a way to check the column certificate for duplicates and remove all but one original??

0
Curtis Long
Asked:
Curtis Long
1 Solution
 
lluddenCommented:
How do you determine the original?  Is there an identity field?

if so, you can use

DELETE FROM plant
WHERE PlantID NOT IN (
SELECT MIN(PlantID), Certificate
GROUP BY Certificate)
0
 
Curtis LongAuthor Commented:
If I have 10 certificates as follows:

10003
10004
10005
10006
10007
10008
10009
10010
10011

Now the certificates 10003 and 10007 are duplicated.

There should NEVER be two of the same numbers in this column.

Is there a way to delete the dulpicate or set it to flag it some how??  IE highlight etc??
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
10003 and 10007 as such are values that are NOT duplicated, so I presume you have some other field(s) that tell you that they are duplicated.

so, you need to specify the "rules" first, and then you can delete all those except one (a first one ...)
to get the list of those with duplicates, aka identify those you consider duplicates, check out this article:
http://www.experts-exchange.com/A_3203.html

to delete all but the duplicates, it could be some code like this:

;WITH CTE AS  (SELECT T.*, 
    ROW_NUMBER() OVER ( PARTITION BY <fields that you consider to specify which rows are duplicates> ORDER BY <fields to specify to tell the rownumber to return 1 for the row you want to keep ...> ) RN
  FROM yourtable AS T
)
DELETE CTE WHERE RN > 1 

Open in new window

all that is written in CAPS in the query shall remain as it is.
all you have to change is the table name itself (replacing yourtable) and putting the columns as indicated.
run. done.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now