Increase price on all products universally?

Works like a charm! Thank you so much! :D
Also work with subtraction just to have said that. :wink:

-There is a " and" to much in your code though. Please edit that.-

Also to be aware of:
When adding a amount, that of course also apply to an attribute with its option value priced to 0.00
The amount is added before any tax.

Thanks again! Now it feels like I might dare to start filling up the shop! :cool:

Hi,

I have a question, how to alter prices for attributes by 9%??

I have a product for which I apply Product Quantity Discounts wenever doing bulk purchases..

Its mentionned in the thread here but am not understanding for which option values is it??
Based on the link http://www.troll-motor.no/shop/index...roducts_id=186[/url]
But I do not know where they use the option_id=22 as a reference, I dont see such id=22..

Thanks

Liizz

Hi again,

Am posting a screenshot for better understanding of wat i'm up to..

I want to increase the attributes prices by 9%..
Want to try out the SQL stuff on it..

Thanks

Liizz

Ajeh, if you're still watching this thread, I'm about to increase all prices on silkblooms.co.uk by a factor of 10%

I don't use price by attribute, no specials either. Everything is just a very basic price.

Am I right that the correct database query would be:

UPDATE products SET products_price = products_price * 1.10;

Also, if we wanted to change the prices back down would I de right to assume the correct querry is:

UPDATE products SET products_price = products_price / 1.10; (with the forward slash/divide sign)?

I have backed up my DB :)

Those statement should do what you want.

Would there be a way to increase all the prices in a single category, but not in other categories? For example, how would I increase the price of every product in cPath=1_7 ??

Also, if I wanted to increase the price by £1.00 each product as opposed to a percentage, how would I do that? Would I use:

UPDATE products SET products_price = products_price + 1.00; ??

......if so, where/how would I add in the category filter?

UPDATE products SET products_price = products_price +1.00 where master_categories_id = 7;

Would this be right?

UPDATE products SET products_price = products_price +1.00 where master_categories_id = 1_7;

OK, I'm getting somewhere:

For sub-category 1_7, this works a treat:
UPDATE products SET products_price = products_price +1.00 where master_categories_id = 7;

However, when I'm trying this on sub-category 1_24_174, this has zero effect:
UPDATE products SET products_price = products_price +1.00 where master_categories_id = 174;

Anyone know why this isn't working?

Make sure that the Products are actually using 174 for the master_categories_id ...

You mean as opposed to being junst 'linked' to that category?

When I listed these particular product, I'd have listed them into category 1_24 and then used the copy-link feature from admin, to link copy them into 1_24_174

Is this why it's not working?

I wouldn't want to increase the price of everything in 1_24, just the ones linked into 1_24_174. Is there a way to do this via SQL patch?

Your SET command is to change prices for Products that use 174 as their master_categories_id ...

If Products are only "Linked" to categories_id 174 then they do not use the master_categories_id 174 ... so their prices will not be changed ...

I see, so is there no way to bulk edit the price on products that are just linked into sub-categories?

Seriously backup your datebase before trying this one:

UPDATE products SET products_price = products_price +1.00
WHERE EXISTS
  ( SELECT products_to_categories.categories_id
    FROM products_to_categories
    WHERE products.products_id = products_to_categories.products_id and products_to_categories.categories_id=174);

NOTE: after running all these price updates you should go to the Tools ... Store Manager ... and run the Update ALL Products Price Sorter

Ajeh, as always; PERFECT!! I think that little snippet of code will be very, very useful to a lot of people. THANK YOU!!

Thanks for the update that this worked for you ...

Just to push this idea further:

To change Tier pricing...

UPDATE products SET products_price = XX.XX where master_categories_id = YY AND products_price = ZZ.ZZ;

Work from the top down.
To avoid rolling all of your tiers to the top.
You won't enjoy changing them all back by hand.

Just wanted to add a piece of MySQL if you wanted to make sure there are no odd four decimal prices as a result of percentage increase.
Prices in Zen cart database come with four decimals XX.XXXX and sometimes this causes rouinding issues with some payment processing services.

UPDATE products SET products_price = ROUND(discount_price * XX.XX,  Y);

Where Y is the number of decimal places. So to preserve whole cents (two decimal places and increase prices by 15%, here is the code:

UPDATE products SET products_price = ROUND(discount_price * 1.15,  2);

Also, if you have quantity based discount with "Actual Price" and not "Percentage" and if you would like to increase those prices, that's in a separate table.
here is a 15% increase with rounding code for that:

UPDATE products_discount_quantity SET discount_price = ROUND(discount_price * 1.15, 2);

Just wanted to add a piece of MySQL to use if you wanted to ensure that there was no four decimal prices (fractional pennies) as a result of percentage increase. Prices in Zen cart database come with four decimals XX.XXXX and sometimes this can cause rounding issues with some payment processing services if that processor completely drops the third and fourth decimal places rather than round.

e.g. $9.99 increase by 15% will result in $11.4885). To fix that use MySQL ROUND function:

UPDATE products SET products_price = ROUND(discount_price * XX.XX,  Y);

Where Y is the number of decimal places to round to. So to preserve whole cents (2 decimal places) and make an increase of 15% the code is:

UPDATE products SET products_price = ROUND(discount_price * 1.15,  2);

$9.99 increase by 15% with ROUND will result in $11.4900 in the database.

Also, if you have quantity based discount with "Actual Price" and not "Percentage" and if you would like to increase those prices, that's in a separate table.
here is a 15% increase with rounding code for that:

UPDATE products_discount_quantity SET discount_price = ROUND(discount_price * 1.15, 2);

Just wanted to add a piece of MySQL to use if you wanted to ensure that there was no four decimal prices (fractional pennies) as a result of percentage increase. Prices in Zen cart database come with four decimals XX.XXXX and sometimes this can cause rounding issues with some payment processing services if that processor completely drops the third and fourth decimal places rather than round.

e.g. $9.99 increase by 15% will result in $11.4885). To fix that use MySQL ROUND function:

UPDATE products SET products_price = ROUND(products_price * XX.XX,  Y);

Where Y is the number of decimal places to round to. So to preserve whole cents (2 decimal places) and make an increase of 15% the code is:

UPDATE products SET products_price = ROUND(products_price  * 1.15,  2);

$9.99 increase by 15% with ROUND will result in $11.4900 in the database.

Also, if you have quantity based discount with "Actual Price" and not "Percentage" and if you would like to increase those prices, that's in a separate table.
here is a 15% increase with rounding code for that:

UPDATE products_discount_quantity SET discount_price = ROUND(discount_price * 1.15, 2);

Don't forget to run Products Price Sorter in Admin->Tools->Store Manager after you have manipulated prices in the database.