We help IT Professionals succeed at work.

duplicate column entries

navajo26354
navajo26354 asked
on
421 Views
Last Modified: 2012-02-08
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.
Comment
Watch Question

Data Engineer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
This is perfect!!!  Thank you so much!!!

Author

Commented:
I have one more question.  For some reason I can't get the delete statement to work.  Here is my statement:
delete  
  from (select *,rn = row_number() over (partition by ArchiveUID order by ObjectID)
          from Synaptic_Indexes_Array021) t1
 where rn > 1

and I am getting this error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 't1'.

Please help
Sharath SData Engineer
CERTIFIED EXPERT

Commented:
;with cte as(select * from (select *,rn = row_number() over (partition by ArchiveUID order by ObjectID)
          from Synaptic_Indexes_Array021) t1
 where rn > 1)
 delete from cte
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.