Safely Deleting Products from the Access DB

[tonygg]

Hi

I need to delete a large number of products from SellerDeck. I would like to do it through access. I am somewhat familiar with the DB structure and know some of the tables to use but I was hoping to get some advice about whether there are records in unusual places or other trace that are left that need to be removed?

I would delete the product (and its duplicates that have *!ProductReferenceNum) by: doing the following in order

1. Removing all records with the PRN in the Product Properties table (for price permutations)
2. Removing all records with the PRN in the User Defined Properties table (for the custom fields)
3. Remove the record from the product table

I don't use the Best Sellers or New Product lists but I did notice some references in the Best Seller List. I guess I need to remove those. As they are auto generated does anyone know if I need to keep the same number of rows or its ok just to delete the row with my reference?

The question is do I need to do anything else?

Many thanks for any advice or help you can give

Tony

[Jan]

Hi Tony,

I would recommend a slightly different approach, rather than removing the products from the database manually, mark them for deletion in the database and then use the option to purge products on the housekeeping menu to actually remove them. That should be a lot safer.

To mark them for deletion change the value of the status field in the products table to "D".

As ever this is still a bit dodgy so take backups of everything before you do anything and follow my advice entirely at your own risk.

Regards,

[chris ashdown]

By far the safest way is to go to content and just right click over the product and "cut" them out from there and after do a compact database

You can select more than one product at a time to either cut or delete and it will automatically remove from marketing lists at compact time

[NormanRouxel]

Another sefer way than database tinkering.

You could export to a Hierarchical File. Then, open that file in Excel and set the "IsDeleted" field on the required products to 1. Then re-import and purge content as per Jan's post.

[tonygg]

Thank you so much Jan, Chris and Norman for you quick and helpful responses

We already Access to change some of the user defined data and stock levels so marking the product for deletion in access and purging in SellerDeck sounds like the way to go.

Just to confirm if I may: I change the status field (status in product table) to text Value D from N and when I next go into sellerdeck it will show these products (with associated permutations and user defined properties) as marked for deletion? If this is correct then it is perfect for us.

Just on the off chance: I wonder if there is a mark for deletion flag in product permutations as well?

REASON: The issue is that there is a known bug in V11 which means the behaviour on ordering a product with only 1 permutation (in product properties table) is unusable. Basically if you only have 1 permutation on the website the permutation is not defaulted and if you just press add to basket Sellerdeck says there is no stock to order. If you have more than 1 permutation visible it defaults to the first in the list and if you just press add to basket it works fine. We start selling things with 3 permutations. As they sell out we reduce to 2 and then 1 permutation visible. The workaround SellerDeck has given us is that we need to delete all the product permutations once we get to only 1 left. I was hoping the mark for deletion might work at this level too?

Many thanks once again.

Tony

[Andy@codepath]

Just to confirm if I may: I change the status field (status in product table) to text Value D from N and when I next go into sellerdeck it will show these products (with associated permutations and user defined properties) as marked for deletion? If this is correct then it is perfect for us.
Tony

Not quite, Tony. If you take this approach you must also set the same flag in all associated records of the productproperty and UDP tables, otherwise the purge of the product, whilst appearing to work normally, will leave behind orphaned records. This can cause problems later if you should add back the same product reference as the old values will automatically be assumed. Even if you never add the product back, it causes unnecessary file bloat and a potential breakdown of referential integrity of the database.

[tonygg]

Andy.

Ok. Thank you

Are there any additional tables that might have orphaned records that would upset SellerDeck?

[Andy@codepath]

Are there any additional tables that might have orphaned records that would upset SellerDeck?

Check for New, Related and Alsobought and obviously don't leave any duplicates behind when you delete their original. Also if you use permutations to refer to associated products, these need consideration too if any product's permutations refer to the one you're deleting. I think that covers it but I have only taken a cursory look. As the others have said - here be dragons!

[tonygg]

Thanks again. They all look clear. Fingers crossed!