0

I am writing this query to display a bunch of Names from a table filled automatically from an outside source:

select MAX(UN_ID) as [ID] , MAX(UN_Name) from UnavailableNames group by (UN_Name)

I have a lot of name duplicates, so I used "Group by"

I want to delete all the duplicates right after I do this select query.. (Delete where the field UN_Name is available twice, leave it once)

Any way to do this?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
HelpASisterOut
  • 3,085
  • 16
  • 45
  • 89
  • 1
    Might be a, heh, duplicate of http://stackoverflow.com/questions/3317433/delete-duplicate-records-in-sql-server – venite Oct 17 '13 at 14:01

2 Answers2

2

Something likes this should work:

WITH CTE AS 
(
    SELECT rn = ROW_NUMBER() 
                OVER( 
                  PARTITION BY UN_Name
                  ORDER BY UN_ID ASC), * 
    FROM dbo.UnavailableNames
) 
DELETE FROM cte 
WHERE  rn > 1

You basically assign an increasing "row number" within each group that shares the same "un_name".

Then you just delete all rows which have a "row number" higher than 1 and keep all the ones that appeared first.

user2023749
  • 75
  • 1
  • 5
2
With CTE As
(
Select uid,ROW_NUMBER() OVER( PARTITION BY uname order by uid) as rownum
From yourTable

)

Delete 
From yourTable
where uid in (select uid from CTE where rownum> 1 )
Mudassir Hasan
  • 28,083
  • 20
  • 99
  • 133