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.
However this take 20-30mins to run....
However If i run the following command
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
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]
)
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Which version of SQL Server are you using?
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.