Remove Duplicates from Two Columns

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

Remove Duplicates from Two Columns

Author

Topic

newnoise
Starting Member

3 Posts

Posted - 2013-05-29 : 18:10:51

I have a database that contains unique IDs in one column and a list of spouse IDs in a separate column. The problem is that these spouse IDs also appear somewhere in the column with unique IDs (i.e., each spouse also has his/her own unique ID). I need code that will look at the values in the spouse ID column and make sure that only 1 spouse from every married couple is retained in the list. Here's a sample of the data:

UniqueID     SpouseID
1               2
2               1
3              NULL
4               15
5              NULL
6               9
7               29
8              NULL
9               6
10             NULL

In the example above, unique ID 1 is married to unique ID 2, unique ID 6 is married to unique ID 9, and unique IDs 4 and 7 are married to people whose unique IDs do not appear in this list of 10. In addition, there are four IDs in the above example that are unmarried (or whose spouses are not in the database).

Any idea of how I can get the above list of 10 to be the following:

UniqueID     SpouseID
1               2
3              NULL
4               15
5              NULL
6               9
7               29
8              NULL
10             NULL

...where unique IDs 2 and 9 have been removed because their spouse is already in the list? (Alternatively, the code could remove unique IDs 1 and 6, just as long as only one spouse per couple remains in the list.)

Any help would be greatly appreciated. Thanks!

SQL Server 2012

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-29 : 22:04:59

[CODE]
DECLARE @TEMP1 TABLE(uniqueID INT, SpouseID INT)
INSERT INTO @TEMP1(uniqueID, SpouseID) VALUES
(1, 2),
(2, 1),
(3, NULL),
(4, 15),
(5, NULL),
(6, 9),
(7, 29),
(8, NULL),
(9, 6),
(10, NULL);

SELECT * FROM @TEMP1 T1 where uniqueID NOT IN (SELECT COALESCE(T2.SpouseID, 0) from @TEMP1 T2 where T2.UniqueID <= T1.UniqueID)

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 01:01:16

[code]
SELECT * FROM @TEMP1 t1 where NOT EXISTS (SELECT 1 FROM @TEMP WHERE UniqueID = t1.SpouseID AND SpouseID = t1.UniqueID )
OR UniqueID < SpouseID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources