?
Solved

SQL Server 2008 search for and remove dulpicates

Posted on 2011-10-31
4
Medium Priority
?
353 Views
Last Modified: 2012-06-27
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
Comment
Question by:Curtis Long
3 Comments
 
LVL 18

Expert Comment

by:lludden
ID: 37056613
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
 

Author Comment

by:Curtis Long
ID: 37064402
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 37064890
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question