Post by ultraspoon » Wed Dec 22, 2010 6:15 pm

I fancy using the Tax module that's built in, but I've already put all my products on the shop with the price including VAT.

Is there a way in Mysql that I can remove 17.5 % off the prices across all products?

Thanks

Active Member

Posts

Joined
Mon Oct 18, 2010 5:56 pm

Post by mystifier » Wed Dec 22, 2010 6:33 pm

Code: Select all

update product set price = round(price/1.175,2)

Free v1.4.9 Extensions: Default Specials | Improved Search | Customer Activity Report | Customer Groups | Royal Mail With Handling | Improved Product Page | Random Products | Stock Report | All Products


User avatar
Active Member

Posts

Joined
Tue May 18, 2010 5:15 pm

Post by ultraspoon » Wed Dec 22, 2010 6:42 pm

Thanks mystifier

Active Member

Posts

Joined
Mon Oct 18, 2010 5:56 pm

Post by ultraspoon » Thu Dec 23, 2010 7:03 pm

Hey mystifier,

Just ran that code in the SQL query, it told me it affected 1673 rows, but my prices haven't changed a thing.

Ive got the import/Export module is there a way I can do it in Excel?

Thanks again dude :D

Active Member

Posts

Joined
Mon Oct 18, 2010 5:56 pm

Post by mystifier » Thu Dec 23, 2010 9:32 pm

If it updated the rows, the prices will have changed. You should see that they are correct in the database product table.

Are you sure you aren't looking at cached content? Aside from your browser cache, featured products, latest products etc., are cached so you may have to delete them in /system/cache.

Product prices obviously won't be changed in orders because the price there is stored as the price at purchase.

Free v1.4.9 Extensions: Default Specials | Improved Search | Customer Activity Report | Customer Groups | Royal Mail With Handling | Improved Product Page | Random Products | Stock Report | All Products


User avatar
Active Member

Posts

Joined
Tue May 18, 2010 5:15 pm

Post by ultraspoon » Fri Dec 31, 2010 12:17 am

Ok you were right Mystifyer, the prices did change in the database, but not on the front of the store, or in the admin section either.

I cleared my browser cache, as well as the system cache, but they are displaying the old prices.

Another problem I have, is that I believe I ran that sql query twice, so It removed 17.5% twice, so yet again, I have the wrong price. can I undo this so It only accounts for one instead of two?

Thanks

Active Member

Posts

Joined
Mon Oct 18, 2010 5:56 pm

Post by jty » Fri Dec 31, 2010 12:45 am

ultraspoon wrote:Ok you were right Mystifyer, the prices did change in the database, but not on the front of the store, or in the admin section either.
this is impossible. are you sure you're looking at the right database
can I undo this so It only accounts for one instead of two?
only if you have a backup. Once sql is run, it's run
another option is to do the math in reverse and run that.

jty
Active Member

Posts

Joined
Sat Aug 30, 2008 8:19 am

Post by ultraspoon » Fri Dec 31, 2010 1:38 am

Haha, Yea, JTR, your right, Im looking at the wrong database. Ill try it again, in the correct one. ???
I do wonder about myself at times.

Active Member

Posts

Joined
Mon Oct 18, 2010 5:56 pm

Post by ultraspoon » Fri Dec 31, 2010 1:50 am

Yup worked like a charm,

Thanks Mystifyer for the sql and thanks jty for making me realise im a tool. :D

Active Member

Posts

Joined
Mon Oct 18, 2010 5:56 pm

Post by ultraspoon » Mon Mar 14, 2011 5:54 pm

Can anybody tell me how to increase by a percentage please?

Active Member

Posts

Joined
Mon Oct 18, 2010 5:56 pm

Post by mystifier » Mon Mar 14, 2011 8:09 pm

It should be simple enough to work out from the example ::) To increase prices by 5% would be:

Code: Select all

update product set price = round(price*1.05,2)

Free v1.4.9 Extensions: Default Specials | Improved Search | Customer Activity Report | Customer Groups | Royal Mail With Handling | Improved Product Page | Random Products | Stock Report | All Products


User avatar
Active Member

Posts

Joined
Tue May 18, 2010 5:15 pm

Post by rrrdv » Tue Aug 06, 2013 3:44 pm

Where I need to put that line ? In which file ? Thank you!

Newbie

Posts

Joined
Wed Apr 10, 2013 1:51 am

Post by amobilept » Sun Jun 01, 2014 7:01 am

mystifier wrote:It should be simple enough to work out from the example ::) To increase prices by 5% would be:

Code: Select all

update product set price = round(price*1.05,2)

I wonder if anyone can help me with an sql query that can acomplish the following as I have to setup wholesale pricing based on cost for a store that has between 5000-7500 products.

product cost * profit margin = wholesale customer group special price

The resulting amounts do not have to be pretty as these would be just for the wholesale customer group.

I know from your post and other sources that to change the main customer price with a percentage I use the following sql query:
UPDATE product SET price = ROUND(price * 1.05, 2)

I do not know too much about mysql but i guess what I wanted was a sql query something more like:
UPDATE product SET special (for specific customer group) = ROUND(cost * 1.5, 2)

Is this even possible to do? Can anyone help me with this query? I didn´t want to mess too much with the database trying to guess since the website is live.

By the way I am still on 1.4.9.4.

Thanks in advance.

Newbie

Posts

Joined
Wed Sep 15, 2010 7:54 am

Post by xseon » Sun Nov 08, 2015 3:00 pm

Here is an extension I wrote for mass product price change.

Deeper and Better Category Module
Mass Product Price Change


User avatar
New member

Posts

Joined
Thu Dec 01, 2011 3:04 pm
Location - Bulgaria
Who is online

Users browsing this forum: No registered users and 26 guests