Solved

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

Posted on 2016-11-28
5
44 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 41904624
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
ID: 41904637
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
ID: 41904678
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
ID: 41905810
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
ID: 41905820
Thanks gentlemen for your outstanding answer and suggestion.  :)
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

738 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