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

[mc12345678]

Something went wrong with post. Need more time to write it again so am deleting this basically.

[mc12345678]

Perhaps unnecessary for you, but others that land on this page, the php version of the statement would be:

<?php 
$cat_holding = 15; // category that contains products to stay in category. 
$db->Execute("delete from " . TABLE_PRODUCTS_TO_CATEGORIES . " where products_id in (select products_id from " . TABLE_PRODUCTS_TO_CATEGORIES . " where categories_id=" . $cat_holding .") and categories_id != ". $cat_holding);

That method will include any table prefix associated with the database and not require consideration/addition of it.

[gilby]

Perhaps unnecessary for you, but others that land on this page, the php version of the statement would be:

<?php 
$cat_holding = 15; // category that contains products to stay in category. 
$db->Execute("delete from " . TABLE_PRODUCTS_TO_CATEGORIES . " where products_id in (select products_id from " . TABLE_PRODUCTS_TO_CATEGORIES . " where categories_id=" . $cat_holding .") and categories_id != ". $cat_holding);

That method will include any table prefix associated with the database and not require consideration/addition of it.

I tried that in a stand alone php file and it didn't work????

<?php 
require('includes/application_top.php');
global $db;

$cat_holding = 15; // category that contains products to stay in category. 
$db->Execute("delete from " . TABLE_PRODUCTS_TO_CATEGORIES . " where products_id in (select products_id from " . TABLE_PRODUCTS_TO_CATEGORIES . " where categories_id=" . $cat_holding .") and categories_id != ". $cat_holding);

Using zen 1.5.1
mysql 5.1.68-rel14.6-log
php 5.4.12
table prefix zen_
I get the same error as when I used the SQL version without an alias

[16-Nov-2014  17:47:44 Australia/Melbourne] PHP Fatal error:  1093:You can't specify  target table 'zen_products_to_categories' for update in FROM clause ::  delete from zen_products_to_categories where products_id in (select  products_id from zen_products_to_categories where categories_id=15) and  categories_id != 15 in  /home/******/public_html/zen151/includes/classes/db/mysql/query_factory.php  on line 120

(The table prefix isn't an issue)
What versions are you using?

[Ajeh]

Out of curiosity, would it work better for you if this feature to move the Product to categories_id 15 and unlink it from all all categories happened when the Order was made by the customer?

This could be customized for when the order is made and eliminate the whole mess and work involved in managing the Sold Out products ...