CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005duplicates
Abhishek B.
Abhishek B.

Reputation: 5202

How to delete all duplicate records from SQL Table?

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.

enter image description here

How I can removed all duplicate combinations from SQL table?

Upvotes: 7

Views: 27083

Answers (5)

Chaman Jain
Chaman Jain

Reputation: 189

Works in Postgres:

DELETE from "FriendsData" where "fID" in
   (SELECT "fID" from
        (SELECT *, ROW_NUMBER() OVER(PARTITION BY "UserID", "FriendsID" ORDER BY  "fID") as rn
    FROM "FriendsData") as inner1
WHERE rn > 1);

Upvotes: 0

Arsen Mkrtchyan
Arsen Mkrtchyan

Reputation: 50752

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

Upvotes: 13

Ben Thul
Ben Thul

Reputation: 32737

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!

Upvotes: 4

gmhk
gmhk

Reputation: 15960

Try this query,

  select * from FriendsData f1, FriendsData f2
  Where f1.fID=f2.fID and f1.UserID  =f2.UserID  and f1.FriendsID  =f2.FriendsID

If it returns you the duplicate rows, then replace Select * by "Delete"

that will solve your problem

Upvotes: 0

Tudor Constantin
Tudor Constantin

Reputation: 26871

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

Upvotes: 1

Related Questions