Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 75
  • Last Modified:

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

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
zimmer9
Asked:
zimmer9
  • 2
  • 2
1 Solution
 
Brian CroweDatabase AdministratorCommented:
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
 
zimmer9Author Commented:
Msg 4112, Level 15, State 1, Line 4
The ranking function "ROW_NUMBER" must have an ORDER BY clause.
0
 
Brian CroweDatabase AdministratorCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
zimmer9Author Commented:
Thanks gentlemen for your outstanding answer and suggestion.  :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now