0121 31 45 374
Qoute Icon

How to: Delete duplicate products from a category in uCommerce

Tim

I was looking into an issue with one of our sites that couldn't delete a product from a category in uCommerce today and noticed that there were a lot of duplicate product-category relationships in the table.

Deleting them is fairly simple so I thought I'd share the code in case you want to clear it down as well.

BEGIN TRAN

-- Check for any duplicates
SELECT
	d.*
FROM (
	SELECT 
		cr.CategoryId, cr.ProductId, cr.CategoryProductRelationId,
		ROW_NUMBER() OVER (PARTITION BY cr.CategoryId, cr.ProductId ORDER BY cr.CategoryProductRelationId) AS Position
	FROM [dbo].[uCommerce_CategoryProductRelation] cr
) AS d
WHERE Position != 1

-- Delete the duplicates
DELETE FROM uCommerce_CategoryProductRelation 
WHERE CategoryProductRelationId IN (
	SELECT
		d.CategoryProductRelationId
	FROM (
		 SELECT 
			cr.CategoryProductRelationId,
			ROW_NUMBER() OVER (PARTITION BY cr.CategoryId, cr.ProductId ORDER BY cr.CategoryProductRelationId) AS Position
		 FROM [dbo].[uCommerce_CategoryProductRelation] cr
	) AS d
	WHERE Position != 1
)

-- Double check the duplicates have been deleted
SELECT
	d.*
FROM (
	SELECT 
		cr.CategoryProductRelationId,
		ROW_NUMBER() OVER (PARTITION BY cr.CategoryId, cr.ProductId ORDER BY cr.CategoryProductRelationId) AS Position
	FROM [dbo].[uCommerce_CategoryProductRelation] cr
) AS d
WHERE Position != 1

ROLLBACK TRAN
< Back to Blog

Liked this post? Got a suggestion? Leave a comment