Removing duplicate records from a table that has no primary key

There are situations where you may have to rethink queries to tackle complex problems. Here is one of them. We had a table in a SQL Server database called NAMELINK where no primary key was defined. The structure was as follows:

NameId1         int       4       not null
Category        char      5       nullable
Relation12      char      20      nullable
NameId2         int       4       not null
Relation21      char      21      nullable
Remarks         char      30      nullable
Dependent       smallint  2       not null

To be more clear, if the values are as follows:

Nameid1 Nameid2
1       2
2       1

Here is the query. It has been tested on SQL Server 7.0:

SELECT NAMELINK.* FROM NAMELINK LEFT JOIN
(SELECT nl1.* FROM NAMELINK nl1 INNER JOIN NAMELINK nl2 on
nl1.Nameid2 = nl2.Nameid1 and nl1.Nameid1 = nl2.Nameid2
) MyNameLink ON Namelink.Nameid1 = MyNameLink.Nameid1
and Namelink.Nameid2 = MyNameLink.Nameid2
WHERE MyNameLink.Nameid1 IS NULL AND MyNameLink.Nameid2 IS NULL
UNION ALL
SELECT nl2.* FROM NAMELINK nl1 INNER JOIN NAMELINK nl2 on
nl1.Nameid2 = nl2.Nameid1 and nl1.Nameid1= nl2.Nameid2
WHERE nl2.Nameid1 = ( SELECT TOP 1 nl3.Nameid1 FROM NAMELINK nl3 INNER JOIN
NAMELINK nl4 ON nl3.Nameid2 = nl4.nameid1 and nl3.Nameid1 = nl4.Nameid2
WHERE (nl3.Nameid1 + nl3.Nameid2) = (nl1.Nameid1 + nl1.Nameid2) ) and
nl2.Nameid2 = ( SELECT TOP 1 nl3.Nameid2 FROM NAMELINK nl3 INNER JOIN
NAMELINK nl4 ON nl3.Nameid2 = nl4.nameid1 and nl3.Nameid1 = nl4.Nameid2
WHERE (nl3.Nameid1 + nl3.Nameid2) = (nl1.Nameid1 + nl1.Nameid2))

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free SQL Server tips and scripts.
• Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
• Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.