CODEWITHSUNDEEP
sql-server
Nict
Nict

Reputation: 3098

Delete duplicates from SQL Server table

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.

Upvotes: 1

Views: 91

Answers (3)

vhadalgi
vhadalgi

Reputation: 7189

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

DEMO

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

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

SQL FIDDLE DEMO

Upvotes: 5

Kalyan
Kalyan

Reputation: 56

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

Upvotes: 1

Related Questions