• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

How do i delete all duplicates and singlise a table

I currently have the following command which i use to to delete duplicates i.e.

delete * from [dbo].[commerce_manufacturer_staging]
where exists (
select *
from [dbo].commerce_manufacturer_staging as a
where a.[Name] = [dbo].commerce_manufacturer_staging.[Name]
and a.[TypeId] = [dbo].commerce_manufacturer_staging.[TypeId]
and a.[id] < [dbo].commerce_manufacturer_staging.[id]
)

Open in new window


However this take 20-30mins to run....

However If i run the following command

SELECT [Name],typeid, count([Name]) FROM 
commerce_manufacturer_staging 
GROUP BY [Name],typeid 
having count([Name]) > 1

Open in new window



This runs a lot quicker, however i cant work out how to delete the duplicates and only have one  instance of name and type per table. Can anyone tell me how i can use this a lot more efficently than what i have done intially?

Thanks
Webbo
0
Webbo_1980
Asked:
Webbo_1980
  • 3
1 Solution
 
David L. HansenProgrammer AnalystCommented:
Try this approach:
http://weblogs.sqlteam.com/MLADENP/archive/2009/01/05/The-simplest-way-to-delete-duplicates-and-compare-two-result.aspx

If you need assistance let me know...glad to help you with the actual writting of the query.
0
 
Webbo_1980Author Commented:
thanks sl8rz this looks perfect however im struggling to adapt it to my example, any chance i could take you on your offer and ask for some guideance / example please?
0
 
David L. HansenProgrammer AnalystCommented:
sure.  Give me a few minutes.
0
 
SharathData EngineerCommented:
try this.
;WITH cte 
     AS (SELECT [Name], 
                typeid, 
                ROW_NUMBER() 
                  OVER(PARTITION BY [Name],typeid ORDER BY id DESC) rn 
           FROM commerce_manufacturer_staging) 
DELETE FROM cte 
      WHERE rn <> 1

Open in new window

0
 
David L. HansenProgrammer AnalystCommented:
Which version of SQL Server are you using?
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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