Friends Table with Duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Friends Table with Duplicates

Author

Topic

lifemode
Starting Member

3 Posts

Posted - 2012-09-24 : 10:58:24

I have a table of friend matches, but it contains reverse duplicates. For example, the table has a row for Bob is friends with Rachel, then later a row for Rachel is friends with Bob. I would like to remove duplicates like this and keep one of those rows (it doesn't matter which) in one master table. Any advice is appreciated!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-24 : 11:19:26

Run the first query to see what is going to be deleted. If you are happy with it, run the second query to delete.

--- 1
--DELETE 
SELECT *
FROM FriendsTable f1
WHERE EXISTS
(
	SELECT * FROM FriendsTable f2
	WHERE 
		f1.FriendA = f2.FriendB
		AND f1.FriendB = f2.FriendA
		AND f2.FriendA > f2.FriendB
);

--- 2
DELETE 
--SELECT *
FROM FriendsTable f1
WHERE EXISTS
(
	SELECT * FROM FriendsTable f2
	WHERE 
		f1.FriendA = f2.FriendB
		AND f1.FriendB = f2.FriendA
		AND f2.FriendA > f2.FriendB
);

lifemode
Starting Member

3 Posts

Posted - 2012-09-24 : 12:04:27

This is exactly what I needed. Many thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources