Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 72
  • 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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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