Pricing Update Query

[mls880]

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

[kylegetson]

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.

[mls880]

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

[haggis-man]

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

[mls880]

Thanks haggis-man

This works like a charm! Much Appreciated.

Marc

[haggis-man]

Most welcome :)

[AndyVI]

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