Friday, September 28, 2012

Deleting Duplicate Bridge Records

When I was importing some data I mistakenly dup'd records into a bridge (many to many) table. I wanted to delete all but the first occurrence of the relationships for the comibination of ids (snowBoarderID,snowBoardID) and made use of the ROW_NUMBER() over in SQL again.
with data as(
 select
  snowBoarderID,
  snowBoardID,
  ROW_NUMBER() over (partition by snowBoarderID,snowBoardID by SnowboarderToSnowboardID asc ) as rownumber
 from SnowboarderToSnowboard
 where isActive = 1
)
select * 
--delete
from data
where rownumber > 1

No comments:

Post a Comment