Mass unlink all Categories from one particular category

[gilby]

On one store I manage
When products are sold out I "disable" them
They are "moved" to a particular "disabled" category for storage
This changes their "master categories id" to this storage category
I would then like to "unlink" all other categories from products in this category
I can do this for particular categories, category by category in Multiple Categories Link Manager

Can anyone suggest a sql to do this in one go?

[mc12345678]

Sounds like this "move" process doesn't move the product to a new category so much as link it to the category. I say that because if it were truly moved, there should be no remnant/reference of the product to a previous category.


The table that contains the information I think you are trying to remove is the products_to_categories table.
Thinking:

delete from products_to_categories where products_id in (select products_id from products_to_categories where categories_id=15) and categories_id != 15;

Where the categories_id of the "disabled" category is 15.

[torvista]

I think this mod will do it:
http://www.zen-cart.com/showthread.p...py-Move-Delete

and this has also some useful features along the same lines:
http://www.zen-cart.com/showthread.p...Manager-Sorted

and yes I think they should both be added to the core!

[gilby]

Sounds like this "move" process doesn't move the product to a new category so much as link it to the category. I say that because if it were truly moved, there should be no remnant/reference of the product to a previous category.


The table that contains the information I think you are trying to remove is the products_to_categories table.
Thinking:

delete from products_to_categories where products_id in (select products_id from products_to_categories where categories_id=15) and categories_id != 15;

Where the categories_id of the "disabled" category is 15.

No the "move" moves the master_categories_id from where it is now to the specified category. Other linked categories remain the same.

Your code inspired me but would not work as written, had to create an alias before it worked :)

delete from products_to_categories
where products_id 
in (select * from 
      (select products_id 
       from products_to_categories 
       where categories_id=15) 
    as old_table) 
and categories_id != 15;

If run from the admin you need to add the table prefix to the second instance of the table reference.
If run from phpmyadmin you need to add the table prefix to both instances of the table reference.

I think this mod will do it:
http://www.zen-cart.com/showthread.p...py-Move-Delete

This is one of my favorite "must have" mods but unfortunately won't do what I need in this case

and this has also some useful features along the same lines:
http://www.zen-cart.com/showthread.p...Manager-Sorted

and yes I think they should both be added to the core!

I have never tried this but will check it out.

Thanks to you both.

[mc12345678]

Well the reason I stated that it doesn't completely move the product is evidenced by the issue being encountered as a change of master_category_id doesn't fully move the product away from it's current category otherwise the issue encountered wouldn't occur.

Interesting the difference in implementation needed for the SQL. The provided SQL works in a $db->Execute() without an additional temporary table. Glad it worked though. (Known to work as have used in further development of Stock By Attributes.)

[gilby]

Interesting the difference in implementation needed for the SQL. The provided SQL works in a $db->Execute() without an additional temporary table. Glad it worked though. (Known to work as have used in further development of Stock By Attributes.)

I didn't try it as part of a php file
I ran it as a sql file
I will keep that in mind if I need to add to my website code