Remove uCommerce Product Definition Field in SQL
Sometimes you need to remove a product definition field from uCommerce e.g. one created in a test environment. Although you can just right click and click "delete" within the administration area, this sometimes doesn't work e.g. when it's a pre-release so this is a simple script which allows you to remove a product definition field from the database.
USE [YourDatabaseName] GO BEGIN TRANSACTION -- Get a list of the current product definitions SELECT * FROM dbo.uCommerce_ProductDefinition DECLARE @ProductDefinitionId int, @ProductDefinitionFieldId int SET @ProductDefinitionId = 23 -- Check that this is the right product definition SELECT * FROM dbo.uCommerce_ProductDefinition WHERE ProductDefinitionId = @ProductDefinitionId -- Get a break down of the various fields for the product definition SELECT * FROM dbo.uCommerce_ProductDefinitionField WHERE ProductDefinitionId = @ProductDefinitionId -- Set the field id SET @ProductDefinitionFieldId = 40 -- Check the right field and descriptions will be removed SELECT * FROM dbo.uCommerce_ProductDefinitionField f INNER JOIN dbo.uCommerce_ProductDefinitionFieldDescription d ON f.ProductDefinitionFieldId = d.ProductDefinitionFieldId WHERE f.ProductDefinitionId = @ProductDefinitionId AND f.ProductDefinitionFieldId = @ProductDefinitionFieldId SELECT * FROM dbo.uCommerce_ProductDefinitionField f INNER JOIN dbo.uCommerce_ProductProperty p ON f.ProductDefinitionFieldId = p.ProductDefinitionFieldId WHERE f.ProductDefinitionId = @ProductDefinitionId AND f.ProductDefinitionFieldId = @ProductDefinitionFieldId SELECT * FROM dbo.uCommerce_ProductDefinitionField WHERE ProductDefinitionId = @ProductDefinitionId AND ProductDefinitionFieldId = @ProductDefinitionFieldId -- Remove any product property definitions DELETE p FROM dbo.uCommerce_ProductDefinitionField f INNER JOIN dbo.uCommerce_ProductProperty p ON f.ProductDefinitionFieldId = p.ProductDefinitionFieldId WHERE f.ProductDefinitionId = @ProductDefinitionId AND f.ProductDefinitionFieldId = @ProductDefinitionFieldId -- Remove any associated descriptions DELETE d FROM dbo.uCommerce_ProductDefinitionField f INNER JOIN dbo.uCommerce_ProductDefinitionFieldDescription d ON f.ProductDefinitionFieldId = d.ProductDefinitionFieldId WHERE f.ProductDefinitionId = @ProductDefinitionId AND f.ProductDefinitionFieldId = @ProductDefinitionFieldId -- Remove the field itself DELETE FROM dbo.uCommerce_ProductDefinitionField WHERE ProductDefinitionId = @ProductDefinitionId AND ProductDefinitionFieldId = @ProductDefinitionFieldId ROLLBACK TRANSACTION -- When you're happy, uncomment this line --COMMIT TRANSACTION
Liked this post? Got a suggestion? Leave a comment