Solved

How to delete duplicate records within a SQL Server 2008 table using a SQL statement?

Posted on 2016-11-28
5
13 Views
Last Modified: 2016-11-29
I am working with a SQLServer 2008 database.

I have a table named "tblBank" with the following 5 fields:

bankID (varchar(50),null)
bankDescr(varchar(50),null)
status(varchar(50),null)
entity(varchar(50),null)
bankAcctNum(varchar(50),null)

Is there a SQL statement to delete any duplicate records within this table?
0
Comment
Question by:zimmer9
  • 2
  • 2
5 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
Comment Utility
WITH cteDuplicate AS
(
	SELECT bankID, bankDescr, status, entity, bankAcctNum,
		ROW_NUMBER() OVER(PARTITION BY bankID, bankDescr, status, entity, bankAcctNum) AS RowNumber
	FROM tblBank
)
DELETE cteDuplicate
WHERE RowNumber > 1

Open in new window

0
 

Author Comment

by:zimmer9
Comment Utility
Msg 4112, Level 15, State 1, Line 4
The ranking function "ROW_NUMBER" must have an ORDER BY clause.
0
 
LVL 34

Expert Comment

by:Brian Crowe
Comment Utility
There was nothing obvious to ORDER BY but you can just throw any column in there if it doesn't matter which record you keep.
1
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
In addition to Brian's correct answer check out the code and image-heavy tutorial SQL Server Delete Duplicate Rows.  Also if you have any feedback please add it to the bottom of the article.  Thanks in advance.
1
 

Author Comment

by:zimmer9
Comment Utility
Thanks gentlemen for your outstanding answer and suggestion.  :)
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now