Hi Guys

I have an e-commerce website. Two of the columns are for the pricing of products. What I would like to do is update the price ('prodprice') directly from the cost price ('prodcostprice').

It would be nice to set a certain markup according to price range. eg. $0-$1000 = 20% $1001-$2000 = 18% etc. and have the ability to put in a cost price, and the system automatically work out the price depending on price range.

In a previous dicussion I have being given:

UPDATE `isc_products` WHERE `prodcostprice` >= 0 AND WHERE `prodcostprice` <= 1000 SET `prodprice` = (`prodcostprice` * 1.20);

UPDATE `isc_products` WHERE `prodcostprice` >= 1001 AND WHERE `prodcostprice` <= 2000 SET `prodprice` = (`prodcostprice` * 1.18);

It seems to generate an error.
Does anyone have a solution or update?
If I could get this to work, what could be easier.. just copy in your new cost prices and update you retail prices automatically. Super

Thanks
Marc

Looks to me like you've got WHERE in there twice. An SQL statement should only have 1. (unless your using join, subqueries, etc.)

I havent tested this but try:

UPDATE `isc_products` WHERE `prodcostprice` >= 0 AND `prodcostprice` <= 1000 SET `prodprice` = (`prodcostprice` * 1.20);

notice, I removed your 2nd WHERE. hope that helps.

Thanks for you help, I will give it a try and post any success stories here!

UPDATE `isc_products` SET `prodprice` = (`prodcostprice` * 1.20) WHERE `prodcostprice` >= 0 AND `prodcostprice` <= 1000 ;

UPDATE `isc_products` SET `prodprice` = (`prodcostprice` * 1.18) WHERE `prodcostprice` >= 1001 AND `prodcostprice` <= 2000 ;

If prodcostprice can have decimal places then you need to edit to handle values between 1000 and 1001

Thanks haggis-man

This works like a charm! Much Appreciated.

Marc

Hi there,

I also require this for my e-commerce website but with more price ranges and percentages but I cant seem to get it working completely.

The price range / % structure I need is.

£0 - £20 = +17.50%
£21 - £40 = +15.00%
£41 - £60 = +12.50%
£61 - £80 = +10.00%
£81 - £200 = +7.50%
£201 - £500 = +5.00%

-------------------------

Here is what I have so far,

UPDATE `products` SET `products_price` = (`products_price` * 1.17) WHERE `products_price` >= 0 AND `products_price` <= 20 ;
UPDATE `products` SET `products_price` = (`products_price` * 1.15) WHERE `products_price` >= 21 AND `products_price` <= 40 ;
UPDATE `products` SET `products_price` = (`products_price` * 1.13) WHERE `products_price` >= 41 AND `products_price` <= 60 ;
UPDATE `products` SET `products_price` = (`products_price` * 1.10) WHERE `products_price` >= 61 AND `products_price` <= 80 ;
UPDATE `products` SET `products_price` = (`products_price` * 1.07) WHERE `products_price` >= 81 AND `products_price` <= 200 ;
UPDATE `products` SET `products_price` = (`products_price` * 1.05) WHERE `products_price` >= 201 AND `products_price` <= 500 ;

But this doesnt seem to be working, it appears to have worked for products priced between 40-60 but anything else seems to have failed to work.

Any ideas?

Many Thanks in advance.
Andy