Solved

VB.net SQL Deleting duplicates

Posted on 2014-12-26
3
397 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
[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
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

749 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