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.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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