Link to home
Start Free TrialLog in
Avatar of William White
William WhiteFlag for United States of America

asked on

duplicate column entries

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.
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of William White

ASKER

This is perfect!!!  Thank you so much!!!
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
;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