There are a lot of posts regarding this issue on Magento. Most of them work but the problem with Categories not resetting their values to ZERO after executing the delete codes/SQL statements still persists and not being addressed up until now.

Does anyone here has the correct code or procedure to properly delete all products from the inventory and have the Category listing also reset to zero after executing it?

9 Answers
9

Regardless of the method used to delete the products, rebuilding the "Category Products" index should fix the counts. Go to System > Index Management. Check the box next to "Category Products", change the action (top right) to "Reindex" and click submit.

Because of the foreign key constraints set up in Magento's database, all other tables that have product information in them are cleaned up nicely. It will of course take some time to delete a lot of products, but it at least gets cleaned up nicely.

If the query can't run because of maximum execution time, you can always run something like:

+1 for using Magento scripting which should preserve data integrity instead of attacking the database, finding things don't work and turning the integrity checks off to find later that things really don't work.
–
Fiasco LabsMar 31 '14 at 15:49

In order to delete products, you can simply use backend:
Catalog > Manage Products > Select all checboxes and choose Action = Delete and hit submit.
This may work very good for some thousands of products.