Q
Problem solve Get help with specific problems with your technologies, process and projects.

Deleting duplicate records with a single query

Hw can I delete duplicate records from a table in SQL Server 2000 using a single query? I have a table (Table1) with two fields: A,B.
 A B 1 a 2 b 3 c 2 b 1 a 4 d
Also, I want to insert a record using a single query that will also check for duplicates, and only if there is no duplicate record, then it should insert.

Another question: I have a table with two fields: BankName and AccountHolder.

 BankName AccountHolder a1 x1 a1 y1 a1 z1 b1 x1 b1 z1 c1 x1 c1 y1 c1 z1 c1 w1
I have to find out the bank name with the second maximum account holders.

First off, you should have constraints on that table in the form of a primary key that does not allow duplicates. That avoids the problem in the first place. There isn't a single, one step query that will both identify and delete only the duplicate rows in that table leaving a single copy. The most straightforward way is to do the following:

 Select distinct col1, col2 Into #temp >From orginaltable Delete from originaltable Insert into originaltable Select col1, col2 >From #temp
For the second question, look up "not exists" in BOL. I don't understand what a second maximum account holder is, so I don't know how to answer that question.

 

For More Information

Dig Deeper on Microsoft SQL Server Installation

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close