How to delete all duplicate records from SQL Table?

Question

Hello I have table name FriendsData that contains duplicate records as shown below

fID UserID  FriendsID       IsSpecial      CreatedBy
-----------------------------------------------------------------
1   10         11            FALSE            1
2   11          5            FALSE            1
3   10         11            FALSE            1
4    5         25            FALSE            1 
5   10         11            FALSE            1
6   12         11            FALSE            1
7   11          5            FALSE            1
8   10         11            FALSE            1
9   12         11            FALSE            1

I want to remove duplicate combinations rows using MS SQL?
Remove latest duplicate records from MS SQL FriendsData table. here I attached image which highlights duplicate column combinations.

How I can removed all duplicate combinations from SQL table?

Solution 1

Try this

DELETE
FROM FriendsData 
WHERE fID NOT IN
(
SELECT MIN(fID)
FROM FriendsData 
GROUP BY UserID, FriendsID)

See here

Or here is more ways to do what you want

Hope this helps

Solution 2

It seems counter-intuitive, but you can delete from a common table expression (under certain circumstances). So, I'd do it like so:

with cte as (
  select *, 
     row_number() over (partition by userid, friendsid order by fid) as [rn]
  from FriendsData
)
delete cte where [rn] <> 1

This will keep the record with the lowest fid. If you want something else, change the order by clause in the over clause.

If it's an option, put a uniqueness constraint on the table so you don't have to keep doing this. It doesn't help to bail out a boat if you still have a leak!

Solution 3

I don't know if the syntax is correct for MS-SQL, but in MySQL, the query would look like:

DELETE FROM FriendsData WHERE fID 
       NOT IN ( SELECT fID FROM FriendsData 
                   GROUP BY UserID, FriendsUserID, IsSpecial, CreatedBy)

In the GROUP BY clause you put the columns you need to be identical in order to consider two records duplicate

Comments

[OMG Ponies]

@Abhishek: This leaves the most recent duplicate, not "remove the latest duplicate" you stated in the question.

[Abhishek B.]

Ok @OMG I got your point. If I change Max() replaced with Min() in above query then it removed latest duplicate records from the SQL table. Am I right?

[Abhishek B.]

Hello @arsenmkrt, I replaced with Max() to Min() in your query then It removes all the latest duplicate records. Am I right?

[Pankaj]

Try with join also :), you can try my response, no matter you have marked this answer :) It can be faster !!

[Ben Thul]

But won't that delete all of the rows in the duplicate set? I think the requirement is to leave one behind.

[Abhishek B.]

Thanks for good reply.. query missing from keyword... " with cte as ( select *, row_number() over (partition by userid, friendsid order by fid) as [rn] FROM FriendsData ) delete cte where [rn] <> 1 " Am I right?

[Ben Thul]

Ah yes... I missed the FROM clause in the CTE. Sorry for that; original post edited.

[shashi]

fID is not contained in an aggregate function or group by clause. Will the above sql statement work as is?

[Ruan Carlos]

I know that it is old but for the record, you need to change the SQL to work in mysql because the error 1093 (Error Code: 1093. You can't specify target table 'xxx' for update in FROM clause) The final sql should be like: DELETE FROM FriendsData WHERE fID NOT IN ( SELECT fID from ( SELECT MIN(fID) AS fID FROM FriendsData GROUP BY UserID, FriendsID ) as something )

[Simas Joneliunas]

Hi and thanks for the answer. Its great that it works for you, but it would help us if you could explain what you did and how did you solve the initial problem!