Need SQL script that will put a price reduction on certain sub-categories.

[Shaun]

Does anyone out there use a SQL script that can be used to put certain categories, or sub-categories on sale for a certain amount of time? For example, if I wanted to reduce the price of Earrings by 10% for the month of October, how would I go about this? Also, how to put it back to how it was?
Please note, this is for price reduction, not "On Sale" as when the date has expired, the 'On Sale" box remains checked.

[rocky]

Make a backup of your ps_product table, then try executing the following SQL query in phpMyAdmin:

UPDATE `ps_product` SET `reduction_percent` = 10, `reduction_from` = '2010-10-01 00:00:00', `reduction_to` = '2010-10-31 23:59:59' WHERE `id_category_default` = 2

Change ps_ to your database prefix and 2 to the ID of the Earrings category. Note that this SQL query relies on all "Earrings" products having the default category set to "Earrings".

[Shaun]

Don't know what I'm doing wrong, but it's not working. I looked in the "ps_category_lang" table to see what the id for earrings was, in this case it is "8".
I then ran the

"UPDATE `ps_product` SET `reduction_percent` = 10, `reduction_from` = '2010-10-01 00:00:00', `reduction_to` = '2010-10-31 23:59:59' WHERE `id_category_default` = 8

query under the SQL tab for "ps_product".
The query looked like it ran fine, but said "O rows affected"?

[rocky]

Is the default category of your "Earrings" products set to category 8? The query checks only the default category, not every category the product is in.

[Shaun]

Mmmm...I guess I'm not sure where to find the "Default category ID" then. What table would this be contained in?

[rocky]

The id_category_default field is in the ps_product table.

[Shaun]

I was preparing screenshots to show you that I only have one default_category_id in my database when I think the light bulb went on (I attached them anyways).
There is only one value for default_category_id which is "1" for all ps_products. I checked all 32 pages of this and it is always the same. So, I checked the BO and see that I must have cheque marked all the products "home" and then cheque marked what category the product belonged to, that is why "1" is the default category for all products.
So, is there any way to target a category still and reduce the price, or is this a lost cause?

[rocky]

You should change all your default categories from the "Home" category 1 to the ID of the subcategory they appear in, otherwise you will have to write a more complicated query. I'm not sure whether you can use LEFT JOIN in UPDATE queries. I've never tried it before.