Solved

VB.net SQL Deleting duplicates

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

831 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