Link to home
Start Free TrialLog in
Avatar of Webbo_1980
Webbo_1980

asked on

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
Avatar of David L. Hansen
David L. Hansen
Flag of United States of America image

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.
Avatar of Webbo_1980
Webbo_1980

ASKER

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?
sure.  Give me a few minutes.
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Which version of SQL Server are you using?