View Full Version : Supplier Price Update

11-12-2008, 03:01 PM
Not sure if this can be viewed as a contribution or the start of a contribution. We run a UK based shop which imports a number of products from suppliers in the US, and as such impacted by changes in the exchange rate. Therefore we required a way to update all prices from a selected supplier by a given percentage rate when ever we feel the exchange rate has changed to much.

Here is a SQL statement that will enable you to do this from phpMyAdmin. I would have liked to have this integrated into the osCommerce Admin pages, but my PHP & SQL skills are not good enough for that.

*PART ONE - run this one line first to give you a list of all manufacturers and their related ID's
SELECT manufacturers_name "Name", manufacturers_id "ID #" FROM manufacturers;

*PART TWO - the price update. Run everything below this line at the same time in the SQL console to update all prices for the manufacturer selected. REMEMBER to change the SET values.
SET @maID = 1; -- Set this value to the ID of the manufacturer you want to change prices for
SET @mult = 1.0; -- Set this value to the multiplier value for the price, e.g. 1.0 is the same, 0.5 halfs price, 2.0 doubles it etc.
UPDATE products SET products_price = (products_price * @mult) WHERE manufacturers_id = @maID;

More... (http://addons.oscommerce.com/info/6362)