Bulk update of prices needed - which table to update thru back end?

[one tall man]

I tried to query my DBs _products table but product_price did not make much sense - there was for example only 1 product with $2.99 but I know for certain that I have tons of such.

Thank you!

[rbarbour]

have you tried ezpopulate or apsona from the plugins

[schoolboy]

You can use SQL queries via INSTALL SQL PATCHES under tools...

BUT...

You must be careful !

There is no "fallback" when using SQL (which is why a DB backup is advised before you install "patches").

It requires a good understanding of SQL - but once you get your head round it, it is quite a useful "tool".

For example, if you wanted to CHANGE all products where the price is 2.99 , to 3.99 ...

... the following SQL will do it:

UPDATE `products` SET `products_price` = 3.99 WHERE `products_price` = 2.99;

Other CONDITIONALS can apply...

UPDATE `products` SET `products_price` = 3.99 WHERE `products_id` = 555; (will change product with ID 555)

It helps to know FIELD NAMES and TYPES in the applicable TABLES, and for "products", these are:

products_id int(11) NOT NULL auto_increment,
products_type int(11) NOT NULL default '1',
products_quantity float NOT NULL default '0',
products_model varchar(32) default NULL,
products_image varchar(64) default NULL,
products_price decimal(15,4) NOT NULL default '0.0000',
products_virtual tinyint(1) NOT NULL default '0',
products_date_added datetime NOT NULL default '0001-01-01 00:00:00',
products_last_modified datetime default NULL,
products_date_available datetime default NULL,
products_weight float NOT NULL default '0',
products_status tinyint(1) NOT NULL default '0',
products_tax_class_id int(11) NOT NULL default '0',
manufacturers_id int(11) default NULL,
products_ordered float NOT NULL default '0',
products_quantity_order_min float NOT NULL default '1',
products_quantity_order_units float NOT NULL default '1',
products_priced_by_attribute tinyint(1) NOT NULL default '0',
product_is_free tinyint(1) NOT NULL default '0',
product_is_call tinyint(1) NOT NULL default '0',
products_quantity_mixed tinyint(1) NOT NULL default '0',
product_is_always_free_shipping tinyint(1) NOT NULL default '0',
products_qty_box_status tinyint(1) NOT NULL default '1',
products_quantity_order_max float NOT NULL default '0',
products_sort_order int(11) NOT NULL default '0',
products_discount_type tinyint(1) NOT NULL default '0',
products_discount_type_from tinyint(1) NOT NULL default '0',
products_price_sorter decimal(15,4) NOT NULL default '0.0000',
master_categories_id int(11) NOT NULL default '0',
products_mixed_discount_quantity tinyint(1) NOT NULL default '1',
metatags_title_status tinyint(1) NOT NULL default '0',
metatags_products_name_status tinyint(1) NOT NULL default '0',
metatags_model_status tinyint(1) NOT NULL default '0',
metatags_price_status tinyint(1) NOT NULL default '0',
metatags_title_tagline_status tinyint(1) NOT NULL default '0',

As I say... SQL commands/queries are totally UNFORGIVING... get it wrong and you can do serious damage to the DB.

Always backup before running sql.

[Ajeh]

If you do a global change on the products_price like this, it is a good idea to go to the Tools ... Store Manager ... and run:

Update ALL Products Price Sorter
to be able to sort by displayed prices:

as manually changing the prices is skipping a step in updating the field products_price_sorter ...

[one tall man]

Figured I was looking at a wrong DB on a wrong server, problem solved, thanks everyone!

[maria82g]

I want to change all prices from $22 to $25 I am using this
UPDATE products SET products_price = 25 WHERE products_price = 22; ,

but prices are not changing.... umm any idea?