Delete the variants (child products) from uCommerce where they don't have a price but the parent is active

Delete variants without prices.sql

BEGIN TRAN

SELECT TOP 100
    *
FROM
    uCommerce_Product p
        LEFT JOIN uCommerce_PriceGroupPrice pgp ON p.ProductId = pgp.ProductId
        LEFT JOIN uCommerce_Product pp ON p.ParentProductId = pp.ProductId
WHERE
    p.ParentProductId IS NOT NULL
    AND pp.AllowOrdering = 1
    AND pp.DisplayOnSite = 1
	AND pgp.Price <= 0


SELECT
    p.ProductId
INTO #VariantsToDelete
FROM
    uCommerce_Product p
        LEFT JOIN uCommerce_PriceGroupPrice pgp ON p.ProductId = pgp.ProductId
        LEFT JOIN uCommerce_Product pp ON p.ParentProductId = pp.ProductId
WHERE
    p.ParentProductId IS NOT NULL
    AND pp.AllowOrdering = 1
    AND pp.DisplayOnSite = 1
	AND pgp.Price <= 0

DELETE FROM uCommerce_ProductProperty WHERE ProductId IN (SELECT ProductId FROM #VariantsToDelete vtd)
DELETE FROM uCommerce_PriceGroupPrice WHERE ProductId IN (SELECT ProductId FROM #VariantsToDelete vtd)
DELETE FROM uCommerce_Product WHERE ProductId IN (SELECT ProductId FROM #VariantsToDelete vtd)

DROP TABLE #VariantsToDelete

SELECT TOP 100
    *
FROM
    uCommerce_Product p
        LEFT JOIN uCommerce_PriceGroupPrice pgp ON p.ProductId = pgp.ProductId
        LEFT JOIN uCommerce_Product pp ON p.ParentProductId = pp.ProductId
WHERE
    p.ParentProductId IS NOT NULL
    AND pp.AllowOrdering = 1
    AND pp.DisplayOnSite = 1
	AND ISNULL(pgp.Price, 0) <= 0

ROLLBACK TRAN