William White
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
delete
from (select *,rn = row_number() over (partition by ArchiveUID order by ObjectID)
from Synaptic_Indexes_Array021)
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
from Synaptic_Indexes_Array021)
where rn > 1)
delete from cte
ASKER