need a complicated SQL query for setting up a sale

[crbsmith]

I am going to be retiring about 1000 products in my store from multiple master categories.

I have created a new master category for the retirement sale and will be setting it at a % off

I want to bulk move items into this category based on the following:
•the product was added over a year ago
•and it sold 2 or less times

I don't have any problem finding the products from the products table that I want to retire using a SQL query
I can also use the same query to set the master category ID to the retirement category
this makes the price update correctly

HOWEVER, the products do not show IN the category yet.

I need to do something to update the products_to_categories where it will insert a new record based on the query above. I am seeing that they will not display in the category until there is a record inserted in this table. But this table ONLY references productID and categoryID.

I only know enough to run a SQL query in one table.

How do I write query to insert a record into products_to_categories based on the date/numbered order selection from the products table?

THANKS

[DrByte]

Try something like:

insert ignore into products_to_categories (products_id, categories_id) select products_id, master_categories_id from products where .......;

[Ajeh]

Do you want the Retired Products to *only* be in the Retired Category or still in the current categories?

[crbsmith]

Do you want the Retired Products to *only* be in the Retired Category or still in the current categories?

It doesn't matter, as long as they go into the retiring category AND price correctly. From what I read in order for the salemaker to work, they would have to have the master category set = the retirement category

[Ajeh]

If you just need to change the master_categories_id after the Products have been added to the Retired category, go to the Retired Categories and click C for Copy and then the Multiple Categories Link Manager ...

At the bottom of the page, you will see:

Reset ALL Products in the selected Category to use the selected Category as the new Master Categories ID ...
Example: Resetting Category 22 would set ALL Products in Category 22 to use Category 22 as the Master Category ID Reset the Master Categories ID for All Products in Category:

This will change all Products in the categories_id that you enter to use that as the master_categories_id ...

[crbsmith]

that only works if ALL the products are from one master category previously and I am doing that for some designers (manufacturers) whose entire product line is retiring but I am pulling products from multiple designers based on sales performance

[DrByte]

In your first post I understood that you had already created a new category for all of these expiring products, and that you'd already set their master-categories-id to that category.

Is that not correct?

You've said a lot of things that I can't tell whether you're confident that you've done them or if you're just talking all hypothetical but have no idea what you're doing.