Ad:

MySQL Discussion Thread
Marked Solved
Views: 984

Sep 17th, 2009

Pricing Update Query

Expand Post »

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:

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.

Similar Threads

Update query in event procedure for Form in Visual Basic 4 / 5 / 6
Update query after cfmail in ColdFusion
UPDATE query problem in ASP.NET
How can i update a query that have more than 3 tables using adodc in vb? in Visual Basic 4 / 5 / 6
UPDATE query not working properly... in PHP

mls880

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Sep 2009

Permalink

Sep 19th, 2009

Re: Pricing Update Query

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:

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.

kylegetson

Reputation Points: 26

Solved Threads: 12

Junior Poster in Training

Offline

89 posts
since Sep 2009

Permalink

Sep 21st, 2009

Re: Pricing Update Query

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

mls880

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Sep 2009

Permalink

Sep 22nd, 2009

Re: Pricing Update Query

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 ;

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

haggis-man

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

19 posts
since Sep 2009

Permalink

Sep 29th, 2009

Re: Pricing Update Query

Thanks haggis-man

This works like a charm! Much Appreciated.

Marc

mls880

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Sep 2009

Permalink

Sep 29th, 2009

Re: Pricing Update Query

Most welcome

haggis-man

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

19 posts
since Sep 2009

Permalink

Mar 16th, 2011

More price ranges

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,

MySQL Syntax (Toggle Plain Text)
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 ;
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

AndyVI

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Mar 2011

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: mysql select from multiple tables / databases

Next Thread in MySQL Forum Timeline: how to insert data into 2 tables by using one sql query?

DaniWeb Message

Cancel Changes

User Name
Password