Bulk Updates

[robsnowden]

Hi guys

I have a client who wants to make changes to 3500 products and I wondered if I could do this quickly using sql queries:

1. Reduce price of every product by 17.5%
2. set every product to 'taxable goods' (currently all product prices are -non-)

Can anybody assist me with the correct sql queries to carry out these changes globally (3500 product would take a long time one by one).

Thank you very much in advance

Rob

[kobra]

Make a DB backup before doing this anytime just in case

UPDATE products SET products_price= products_price * .825;

UPDATE products SET products_tax_class_id=1;

[robsnowden]

Thanks very much kobra

I appreciate your prompt help.

Rob

[robsnowden]

I tried the first sql query and it came back telling me that this table does not exist. I can ceratinly not see it in the list.

Also I have the prefix zen_ so where do I put that bit in the queries please?

Any ideas?

Rob

[robsnowden]

I figured it out - doh!

Rob

[Beavo1]

Hi guys

I have a client who wants to make changes to 3500 products and I wondered if I could do this quickly using sql queries:

1. Reduce price of every product by 17.5%
2. set every product to 'taxable goods' (currently all product prices are -non-)

Can anybody assist me with the correct sql queries to carry out these changes globally (3500 product would take a long time one by one).

Thank you very much in advance

Rob

Was looking to do something similar and you may have realised this by now but if you were looking to strip out the VAT element of what were originally gross prices, using 17.5% reduction will leave you with an incorrect net price. VAT is calculated as a % of the net (exclusive) price what you are doing is reducing the price by 17.5% of the gross (inclusive).

For example 100 x 1.175 is 117.5 (you have added 17.5% to 100) but 117.5 x 0.825 will not get you back to 100...

What you need to do is reduce the gross prices by 7/47ths of the gross which would give you the correct net price.