Removing duplicate records from a table that has no primary key

A query to find composite primary keys.

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
While converting to another database, we assumed that NameId1 and NameId2 are composite primary keys. There may be some cases where they aren't unique. Now we wanted a select query to fetch only those records where the combination of NameId1 and NameId2 is unique.

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

Nameid1 Nameid2
1       2
2       1
Then the select query should fetch only one combination and not both.

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.

This was first published in December 2002
This Content Component encountered an error

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchCloudComputing

SearchSoftwareQuality

SearchSOA

TheServerSide

SearchCloudApplications

Close