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,438
  • 68
  • 55
  • 126
HelpASisterOut
  • 2,975
  • 15
  • 42
  • 79
  • 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
  • 26,910
  • 19
  • 95
  • 126