Delete duplicate records and keep one

Question

Using T-SQL.

My problem is I have a list of usersid and locationid. A user can only have one location.

With the following query I get a list with all users with multiple location id's:

SELECT * 
FROM Userslocation
WHERE [USER] IN (
    SELECT [USER]  
    FROM Userslocation
    GROUP BY [User] 
    HAVING COUNT(*) > 1
    )

as result I get

location User
80653    83269
63150    83269
80653    84035
63150    84035
80653    85097
63150    85097

I want to get preserve one result and delete the rest

location User
80653    83269
80653    84035
80653    85097

How do you decide which records to keep...and which to delete? — Mr Moose, Jul 09 '15 at 06:32

score 1 · Answer 1 · answered Jul 09 '15 at 06:38

1

You'll need something like a CTE

WITH UserslocationCTE AS (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY [User] 
    ORDER BY location)'RowRank'
    FROM Userslocation)
DELETE FROM UserslocationCTE 
WHERE RowRank > 1

answered Jul 09 '15 at 06:38

Shiva

20,575
14
82
112

Thanks this works :D – H. Schutte Jul 09 '15 at 07:46
Great. Please mark it as the answer then – Shiva Jul 09 '15 at 15:15

score 0 · Answer 2 · answered Jul 09 '15 at 06:33

0

Return each user once, with the user's max location:

select user, max(location)
from tablename
group by user
having count(*) > 1

answered Jul 09 '15 at 06:33

jarlh

42,561
8
45
63

nayef harb · Answer 3 · 2015-07-09T07:33:21.723

Insert an Incremental Id for the usersLocation. Then Delete the records which are greater than the first ID. for example.

Query
___
SELECT * 
FROM Userslocation
WHERE [USER] IN (
    SELECT [USER]  
    FROM Userslocation
    GROUP BY [User] 
    HAVING COUNT(*) > 1
    )

will show the below result

ID   location User
1    80653    83269
2    63150    83269
3    80653    84035
4    63150    84035
5    80653    85097
6    63150    85097

now you can do

 SELECT *
--or delete 
FROM Userlocation
WHERE id NOT IN (
        SELECT MIN(id)
        FROM Userlocation
        GROUP BY [USER]
        HAVING COUNT(*) > 1

        UNION

        SELECT MIN(id)
        FROM Userlocation
        GROUP BY [USER]
        HAVING COUNT(*) = 1
        )

Delete duplicate records and keep one

3 Answers3