How to do a mass price increase by product price

Hi,

We are are running v.1.5.5.e and are looking to do a mass price increase. For example we are looking to change prices on products in a specific category where there are products priced at $6.99 (want to increase to $7.99) and also products priced at $15.96 (not increase by the same percentage). Updating via a percentage does not give us the proper increase we are looking for.

Is there a way to tell zen cart that in Category 10 to increase all products currently priced at $6.99 to $7.99?

Thanks in advance for any help and advice,

Jeff

Is it all product in a category (those specifically assigned to the category as well as linked to the category) or all product that have a master_categories_id of 10 to which the price change is to be applied?

For product that are specifically assigned to category id 10 (not linked), the following could be executed to change the price of product priced at $6.99 to $7.99:

UPDATE products SET products_price=7.99 WHERE products_price=6.99 AND master_categories_id=10;

Carefully make a backup before doing this step!

Hi swguy,

Thanks so much for the code and yes, the products that I am looking to change the price in are all in master category 10, although they are linked to other sub categories in this same master category 10. I hope that makes sense, and will this cause any problems being that they are linked to other sub-categories??

Also, will I have to run the the All Products Price Sorter in Admin->Tools->Store Manager once running this SQL stmt for it all to take effect??

Thanks again,

Jeff

NWCE:

Hi swguy,

Thanks so much for the code and yes, the products that I am looking to change the price in are all in master category 10, although they are linked to other sub categories in this same master category 10. I hope that makes sense, and will this cause any problems being that they are linked to other sub-categories??

Also, will I have to run the the All Products Price Sorter in Admin->Tools->Store Manager once running this SQL stmt for it all to take effect??

Thanks again,

Jeff
Running the sql statement will update the price of all of those product wherever they are located. Yes, it would be a good idea to run the price updater operation after running the sql statement in particular if there are any existing sales or specials associated with the product. As far as problems, well two things, as I forgot to mention the database backup, if anything is seen immediately, it can be reverted. With regard to the product centralized in master category 10 but linked to other categories, the price will then be seen in the related category(ies). Only real problem I could see with that is say if you had a category with this product that is say titled: 'Everything under $7'... obviously an "issue" because then the product would be more than $7...

Note: the ideal way to determine the possibility of a problem would be to do this on a backup of the store and test, test,

Thanks for your reply....I do appreciate it and for sharing this code with me. This is will be huge time saver....and I totally hear you about backing up before running this SQL statement.

Thanks once again for your help,

Jeff

Hi swguy,

I have backed up my database and run the script that you suggested, making the necessary changes to the prices and master_category_id to be correct for my store and once run I also ran the ALL Products Price Sorter, but it has not made the change. I received the "1 statement" processed in green when I ran the SQL stmt and also a confirmation on the All Products Price Sorter.

I was running this as a test for tomorrow's planned price updates and here is a direct link to my store so that you can see that I have the correct code of....
UPDATE products SET products_price=4.99 WHERE products_price=5.99 AND master_categories_id=23;
If you look at this page, https://www.nitwitcollections.com/shop/index.php?main_page=index&cPath=23_26&sort=20a&page=2 you will see products priced at $15.96, $6.99 and $5.99. I was only focusing on the products priced at $5.99 for this test and as you can see from the URL link this is my master category 23, sub-category 26.

I know that I am obviously doing something wrong, and am hoping that you can make a suggestion so that I can proceed with my storewide pricing updates.

Thanks again for your help.

Jeff

My guess would be that the master categories id is not what you think it is. Verify this in phpMyAdmin.

Actually, you have the master and sub reversed. The last category in the list is the master category, all previous values are the parent to that. The sql statement should have been run against category 23.

Back somewhere in 1.3.x and/or before, the sequence was as you described, but it got reversed later.

swguy:

My guess would be that the master categories id is not what you think it is. Verify this in phpMyAdmin.

It can/should also be visible by going to the product in the admin and seen in the upper left corner. If it is linked then it can be changed, if not, then it is set at the current master category id.

Hi swguy,

Thanks for your reply and suggestion on confirming the mast categories id. I have verified it in PHP MyAdmin and it's id is 23. Any other suggestions??

Thanks,

Jeff

Hi mc12345678,

Thanks for you hint as to the changes of the category listing and where to easily find it in Admin. I have checked this and in Admin it says that it is in master ID 32 which in the store side is a subcategory of ID 23. My category 23 is Digital Kits and category 32 is a subcategory labeled Baby/Kids. I will try this and let you know if this works!

Thanks for your help,

Jeff

Thank you both swguy and mc12345678,

Yes, in using the subcategory ID this has worked perfectly! Thank you both for your help and suggestions.....I appreciate it!

Thanks again,

Jeff

NWCE:

Thank you both swguy and mc12345678,

Yes, in using the subcategory ID this has worked perfectly! Thank you both for your help and suggestions.....I appreciate it!

Thanks again,

Jeff
Glad that were able to finally figure it out. Have you gone back to see how/why a few posts ago the id was identified as 23? Was it a moment of number dyslexia or possibly looking at the wrong table/field? Swguy's suggestion certainly offered a learning opportunity and should have identified 32 as the master_categories_id for the associated product.

For others that might have found this thread and possibly also had a problem, my next suggestion was going to be to look at the products_price of the product. Reason? If the store was set to show prices with tax already included and the taxed price was entered as 6.99, then the stored price would have excluded the tax and been some number lower... but in this situation, that additional effort was not needed. :y

Thanks for your follow up mc12345678. What it is grabbing as the master_categories_id is the subcategory category number. I misunderstood, thinking to use the parent category (i.e. Digital Kits in my store is the parent category #23 and Baby/Kids is the subcategory #32). I thought that by using the parent category number in the SQL code as the master_categories_id it would go through all sub categories as complete the action. This is simply my lack of SQL code syntax.

As well, I hope that this thread can help others!

Thanks again for the follow up and clarification,

Jeff

So, I had hoped to test this before posting it, but was unable due to time and resource constraints. So, it seems that you would like to be able to update prices in more than one category at once. Specifically you would like to be able to be looking at a single category that has one level of sub-categories below it. In those sub-categories (again only one level deeper than the currenct category) you would like to update all of the product prices. Already a SQL statement has been provided to address all of the product that are associated to a single category. Also, understand that the ZC design is such that it is expected that product are not assigned to a category that already has sub-categories (and that sub-categories not be placed in a category that already has active product).

The below has three "editable" pieces of information. The first is the target or end price, the second is the parent category that only has one level of categories below it, and the last is the price of product to be changed.

UPDATE products p SET p.products_price = 7.99 WHERE p.master_categories_id IN (SELECT c.categories_id FROM categories c WHERE c.parent_id = 23) AND p.products_price = 6.99;‎

Again this is untested and may need modification to even work in mysql (or possibly any sql language. :P) As swguy reminded/suggested, backup your database before testing this and verify operation immediately after to determine if the database needs to be restored.

Regarding the discussion of categories, internally ZC uses the master_categories_id as the product's "home". It is the category that when altered will cause changes to be seen anywhere the product is displayed (linked product).

Hi mc12345678,

Thanks for your message and further explanation on the categories. Also thank you for the newly suggested SQL code. I am fine using the original code as it accomplishes the price changes, and will work for my situation. I now know which category id to place into the code to accomplish the price changes in the specific categories that I am addressing.

Thanks once again for your help,

Jeff