SQL Patch question - help please!

Hi,

I have a huge # of products (too many to do manually) that need to be moved to new categories. I will then go back and delete the old categories, there are about 40 categories that need to go.

Can somebody show me how I could write a little script I could send thru the SQL Patch that would change the Category ID for all prod in a specific category to a different Category ID?

And maybe a second script that would erase the soon to be empty categories?

++++
My preference would be to search the product DB and do like a Find/Replace.

FIND: Category ID xxx

REPLACE WITH: Category ID yyy

It would be lovely if I could wrap numerous Find/Replace strings into a single statement! If you did a sample that changed 2, I could replicate the syntax to get them all done in one shot.
++++

++++
Alternatively, I have all my Product IDs. So if, because of table relationships, it was necessary to lookup the product first, then alter the associated Category ID, that would work, too. A bit messier, but it could be done, np. Again, if your sample showed how to change 2, I could replicate your syntax to do all the products.
++++

Thank you in advanced! I'm totally desperate here, back against the wall with a huge deadline. I really appreciate you looking at this for me! :smile:

Thanks,
Mike

First, if the category you're wanting to move all these products to DOES NOT YET EXIST, you can just move that category to another parent, and rename it if necessary. Bam, done!

But if the destination category already exists and its category-id is important to retain as-is, then you'll need something more complex, like the following:

The category is stored in a couple ways:

1. In the products table there is the master_categories_id ... which basically says which primary/main category the product belongs to
2. And in the products_to_categories table there is an entry for EVERY Product + Category relationship. So if a product appears in multiple categories (ie: is linked to more than one category) then there will be multiple entries in this table, one for each product+category combination.

So, before doing such a mass change, you need to first make sure that the products whose categories you want to make mass changes to are NOT ALREADY "linked" to the destination category you intend to move them to.
One way to do that is to use your Admin and go to that destination category and check whether any of the "linked" products (denoted by the yellow bubble) show there. If they are, then you may want to manually change those by using your Admin to edit that product and change its master and then unlink it (delete it from the other category).

Once your inspection confirms that none of those products are already linked to the destination category, you can do something like the following to MASS MOVE products out of one category and into another;

update products_to_categories set categories_id=NEWNUMBER where categories_id=OLDNUMBER;
update products set master_categories_id=NEWNUMBER where master_categories_id=OLDNUMBER;

You'll get duplicate-key errors if you run that first statement and it finds any of those products already linked to the new category.

Then go and manually delete the empty/obsolete categories using your Admin screen. It's better to do this from your Admin because it does more than just SQL cleanup (ie: also cleans up images, descriptions, languages, related restrictions, and more).

Yippee, thanks Doc!

I just double checked, no linked products. In every case, I will simply be moving products up one level (combining 10 or so subcategories at a time into single higher category). The categories already exist, but I could easily create new categories, then rename them after I have deleted the originals if you think that's the best way to do it. But I'm unclear how to accomplish that... though your summary is very appealing (Bam, done!)! :D

On the other hand, your mass move instructions seem pretty simple, too. Can I just string a bunch of them together like and past them into the SQL patch like this:

update products_to_categories set categories_id=NEWNUMBER where categories_id=OLDNUMBER;
update products set master_categories_id=NEWNUMBER where master_categories_id=OLDNUMBER;
update products_to_categories set categories_id=NEWNUMBER where categories_id=OLDNUMBER;
update products set master_categories_id=NEWNUMBER where master_categories_id=OLDNUMBER;
update products_to_categories set categories_id=NEWNUMBER where categories_id=OLDNUMBER;
update products set master_categories_id=NEWNUMBER where master_categories_id=OLDNUMBER;

Feznizzle:

In every case, I will simply be moving products up one level (combining 10 or so subcategories at a time into single higher category). The categories already exist, but I could easily create new categories, then rename them after I have deleted the originals if you think that's the best way to do it. But I'm unclear how to accomplish that... though your summary is very appealing (Bam, done!)!"combining" categories can't be done by the simple "move" I was talking about above.

Feznizzle:

On the other hand, your mass move instructions seem pretty simple, too. Can I just string a bunch of them together like and past them into the SQL patch like this:

update products_to_categories set categories_id=NEWNUMBER where categories_id=OLDNUMBER;
update products set master_categories_id=NEWNUMBER where master_categories_id=OLDNUMBER;
update products_to_categories set categories_id=NEWNUMBER where categories_id=OLDNUMBER;
update products set master_categories_id=NEWNUMBER where master_categories_id=OLDNUMBER;
update products_to_categories set categories_id=NEWNUMBER where categories_id=OLDNUMBER;
update products set master_categories_id=NEWNUMBER where master_categories_id=OLDNUMBER;

Yes.

Here's hoping Ajeh doesn't tell me otherwise!
But she's offline today, so we'll have to play without her. :(

Of course, always make backups before doing these kinds of things!

Addendum: After doing this, if you have any pricing features (discounts, sales, specials, etc) specific to the categories you're moving "from" or "to" (or the parents under which they are found), then you'll want to use the Store Manager screen to "Update All Products Price Sorter" so that it recalculates pricing cache data for searches and sorts. Otherwise product price info may be outdated on the storefront.

None of the products are subject to category specific features. However, all have qty based discounting and most have cross sells attached to them. Doesn't seem like any of that will be effected.

Of course, always make backups before doing these kinds of things!

Haha. Man, I should earn frequent flyer miles on your Backup mySQL mod, as heavily as I lean on that thing! :D

Worked like a charm, DrByte! Thank you so much!!!

Now I'm just hoping Ajeh doesn't come back and say this did terrible damage to the db! hahaha :D

I asked her to double-check, and she said I did good. :blush:

Plus I double-checked the actual PHP code that handles that stuff for one-off cases in the admin ;)

Never a doubt in my mind. Thanks again, Chris!

NEW QUESTION:

I'm trying to remove some problems by doing a find/replace. Is this correct?

	update categories_description set categories_name = REPLACE( [B]`[/B]categories_name[B]`[/B] ,  '&',  '&' )

As long as each-and-every-one of the ampersands in your categories' names are singular, e.g. This & That. If any of your categories' names already include the htmlentities, e.g. This & That, then your query will result in the & being double-escaped: This & That.

Thinking could add a criteria to the where statement (or pretest for such existing conditions) such as

AND categories_name not like '%&%'

You could, but what about the other HTML symbols, e.g. <? That will be converted to <.

Haha! I found that out the hard way!

It worked for categories because no other instances of '&' existed, such that it was being used for html entities. So I then applied it to a zillion product titles, many of which were using it.

DOH!

Thank you so much for your input!