troubleshooting Question

Problem with duplicate delete

Avatar of navajo26354
navajo26354Flag for United States of America asked on
Microsoft SQL Server 2008
4 Comments1 Solution254 ViewsLast Modified:
I asked this question earlier and received a very good answer.  However the last part, where I am instructed how to delete, gives me an error.  Can someone read over this and tell me what is wrong with the delete statement?

I have a two column table.  In column1 I have duplicate userID entries.  In column2 the values are distinct with the exception of one or two.  I want to get rid of all duplicate values in column1 and then make column1 a primary key.  Before I get rid of them I'd like to identify all of the duplicate column1 values and place each record with a duplicate column1 value into a new temporary holding table.  Can anyone provide me with a script to do this?  All the scripts I have found assume both column1 and column2 have duplicates (a duplicate record) but this is mostly not the case for me.  I'm only concerned with the duplicates in column1.  Column 1 has a primary key on it.

this gives the distinct records for each column1.

select *
  from (select *,rn = row_number() over (partition by column1 order by column2)
          from your_table) t1
 where rn = 1

this gives the duplicate entries for each column1.

select *
  from (select *,rn = row_number() over (partition by column1 order by column2)
          from your_table) t1
 where rn > 1

load these duplicate entries into another table.

select * into another_table
  from (select *,rn = row_number() over (partition by column1 order by column2)
          from your_table) t1
 where rn > 1

and then delete the dupes from your original table

delete  
  from (select *,rn = row_number() over (partition by column1 order by column2)
          from your_table) t1
 where rn > 1
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros