Solved

VB.net SQL Deleting duplicates

Posted on 2014-12-26
3
383 Views
Last Modified: 2014-12-26
Hi

In my VB.net project I pull the data shown below into  a DataGridView using the following SQL statement.
It shows all records with the same Article and  Site combination. How do I delete all duplicates leaving one record
containing the combination

select rtrim([Article])+rtrim([Site]),count(*) As Count_Duplicates from [MARC] group by rtrim([Article])+rtrim([Site]) having count(*)>1

1
0
Comment
Question by:murbro
  • 2
3 Comments
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 40518393
What SQL Server version?

E.g. using ROW_NUMBER()

 
WITH Ordered AS 
	(
		SELECT 	Article,
			Site,
			ROW_NUMBER() OVER ( PARTITION BY Article, Site ORDER BY anotherColumn ) AS RN
		FROM 	MARC 
	)
	DELETE FROM Ordered
	WHERE	RN > 1;

Open in new window


Caveat: make a backup first.
0
 

Author Comment

by:murbro
ID: 40518452
Hi Ste5an. That looks good, I am using SQL 2008
0
 

Author Comment

by:murbro
ID: 40518475
thanks
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

895 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

15 Experts available now in Live!

Get 1:1 Help Now