MySQL

Pricing Update Query

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Sep 2009

Posts: 4

Reputation: mls880 is an unknown quantity at this point

Solved Threads: 0

mls880

Offline

Newbie Poster

Pricing Update Query

Sep 17th, 2009

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:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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);
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

Last edited by peter_budo; Sep 23rd, 2009 at 4:36 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Sep 2009

Posts: 62

Reputation: kylegetson is an unknown quantity at this point

Solved Threads: 9

kylegetson kylegetson is offline

Offline

Junior Poster in Training

Re: Pricing Update Query

Sep 19th, 2009

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:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
UPDATE `isc_products` WHERE `prodcostprice` >= 0 AND `prodcostprice` <= 1000 SET `prodprice` = (`prodcostprice` * 1.20);
UPDATE `isc_products` WHERE `prodcostprice` >= 0 AND `prodcostprice` <= 1000 SET `prodprice` = (`prodcostprice` * 1.20);

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

Don't pay data charges. txtFeeder.com is a free way to read the web on your mobile, and avoid data charges! **Now txtFeeder has a wireless note feature! Make notes on the go!
-Kyle Getson

•

Join Date: Sep 2009

Posts: 4

Reputation: mls880 is an unknown quantity at this point

Solved Threads: 0

mls880

Offline

Newbie Poster

Re: Pricing Update Query

Sep 21st, 2009

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

•

Join Date: Sep 2009

Posts: 15

Reputation: haggis-man is an unknown quantity at this point

Solved Threads: 1

haggis-man haggis-man is offline

Offline

Newbie Poster

Re: Pricing Update Query

Sep 22nd, 2009

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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 ;
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 ;