Thread: SQL Bulk Delete

Hi

We want to remove a whole manufacturers range from our site and database but are unsure of the best way of doing this.

Ideally we need to remove the product variants, then the products and then finally the manufacturer

Is there an SQL statement that can achieve this?

Thanks in advance.....

In your SQL Enterprise Mgr run the following query and make sure you see the products you want to delete:

select * from product p
join productmanufacturer pm on p.productid = pm.productid
and pm.manufacturerid = (select top 1 manufacturerid from manufacturer where name = 'ManufacturerToEventuallyDelete')

If you only see products you wish to delete, then run this:

update product set deleted = 1 where productid in (select p.productid from product p
join productmanufacturer pm on p.productid = pm.productid
and pm.manufacturerid = (select top 1 manufacturerid from manufacturer where name = 'ManufacturerToEventuallyDelete'))

update productvariant set deleted = 1 where productid in (select p.productid from product p
join productmanufacturer pm on p.productid = pm.productid
and pm.manufacturerid = (select top 1 manufacturerid from manufacturer where name = 'ManufacturerToEventuallyDelete'))

update manufacturer set deleted = 1 where name = 'ManufacturerToEventuallyDelete'

Hope that proves useful.

As always, please ensure that you have a full, functioning backup before making any code modifications or file changes to your AspDotNetStorefront website, or running any ad-hoc queries against your database. Improperly making code modifications or running queries against your database can cause your website to be non-functional and/or your data to be permanently lost. As part of your disaster recovery plan, you should make regular backups of all files and data, and perform periodic checks to ensure your backups function properly. If you are not sure if your data is adequately protected, contact an IT professional or your hosting provider for assistance.

I have a related question.

When deleting a single product programatically, is it okay to just set the Deleted field to '1' in the Product table? Or are there other changes needed in the database? I see in the previous posts a suggestion of setting the deleted field in the ProductVariant table as well. Is that really necessary?

Thanks.

well, nuking a product (deleting) it is a bit more complicated, you need to:

a) delete the product rec
b) delete variant recs
c) delete (ALL) mapping recs
d) delete extended (customer level/wholesale/retail) pricing recs
e) have all carts updated (they might have contained it)
f) delete product rating recs
g) etc...

I thought we had a proc that was able to do this?

Thanks for the quick reply.

I should have been clearer in my earlier post. I'm not concerned about leaving clutter in the database, I just want the product to be 'deleted' in the same way it is 'deleted' when I delete it from the admin site.

When I delete a product from the admin site, it changes the Deleted flag in the Product table to '1' and there are no obvious changes to either the ProductVariant or ExtendedPrice tables. If I want to replicate programatically with some custom code the same changes made when I delete a product from within the admin site, is there anything I need to change in the database other than switching the deleted flag in the record in the Product table?

Nope. that's it then. Set Deleted=1 in product table.

Thanks again. A client wants a bid on some work and I wanted to know the surprises before I lock in a price.

always wise to do