I need to remove some duplicate entries from an intersection table.
The table is incredibly badly set up, without primary keys, so I'm having some trouble removing entries which are duplicates.
Here's just a rough overview of the table:
col1 col2
------------
1 70
1 70
1 71
Both columns carry id's, and duplicates breaks stuff.
You can use RANKING Functions
with cte as
(
select row_number() over(partition by col1,col2 order by col1,col2 )as rowNum
from tableName
)
delete from cte where rowNum>1
with t1dups (col1, coldups)
AS (
select col2, ROW_NUMBER() Over (Partition by col1, col2 order by col2) as dups from t1 )
delete from t1dups where coldups > 1
drop table #t
create table #t(col1 int,col2 int)
insert into #t values(1,70),(1,70),(2,71)
;with cte as
(
select [col1],[col2],rn=row_number() over(partition by col1 order by col2) from #t
)
delete from cte where rn>1
select * from #t
