Adjusting the price of an Atribute in Batch

On our site we sell leather clothing. We have placed all the items in the store and priced them using one set price for each product. We have size attibutes from Small to 10 XL and would like to increase the price of the sizes of 5 XL to 10 XL by $20.00 since that is what we are charged. Is there any way I can do this in a batch mode by the size? (ie Make all 5 XL's + $20.00)

Thanks
Mike

I did this the other day for a client,
got to Admin -> Catagories/Products -> Option value Manager, and get the id of the XL value from the far left column.
In the below code replace YOUR_PRODUCTS_VAULE_ID w/ the id that you got.
got to admin-> tools -> install sql patches. and run the statement that you just made
be sure to backup you sql before doing this but you can run

UPDATE `products_attributes` SET `options_values_price` = '20.0000' WHERE options_values_id = "YOUR_PRODUCTS_VAULE_ID";

Thanks for the reply, that worked GREAT!! One other thing I had to do was to run the same script and change the price prefix since all my numbers were coming up as negatives.

For those having this problem I ran:

UPDATE products_attributes SET price_prefix = '+' WHERE options_values_id = "YOUR_PRODUCTS_VAULE_ID";

and substituted "YOUR_PRODUCTS_VAULE_ID" with the actual product ID number from the store (ie '45')

Thanks again

oh sorry I had assumed that it was already at positive.

That's okay, I didn't realize it till after I ran the script and for me it was a good execise in working with databases and figuring it all out. I posted what I did for other people who may run into the same problem.

Mike

sanguisdesign:

I did this the other day for a client,
got to Admin -> Catagories/Products -> Option value Manager, and get the id of the XL value from the far left column.
In the below code replace YOUR_PRODUCTS_VAULE_ID w/ the id that you got.
got to admin-> tools -> install sql patches. and run the statement that you just made
be sure to backup you sql before doing this but you can run

UPDATE products_attributes SET options_values_price = '20.0000' WHERE options_values_id = "YOUR_PRODUCTS_VAULE_ID";

Thanks, that was great.

Could something similar be done for sort order?

Many thanks,
Rick

EyefulTower:

Could something similar be done for sort order?
Many thanks,
Rick
Yeap sql scrptings is great for bulk updates

sanguisdesign:

Yeap sql scrptings is great for bulk updates

What is the label for sort order? I haven't looked in the database yet.

would it be too simple to assume its the following?

UPDATE products_attributes SET options_values_sort_order = '20.0000' WHERE options_values_id = "YOUR_PRODUCTS_VAULE_ID";

well if you know how to look it it up I would suggest that you look it up. the best way to get comfortable w/ Mysql is to use it.

sanguisdesign:

well if you know how to look it it up I would suggest that you look it up. the best way to get comfortable w/ Mysql is to use it.
I haven't got mysql, its on my to do list.

A blast from the past, but....

Could you tell me how to do this using the Options Name ID instead of the Options Value ID?

Would this change it to be the options name ID instead of the value id?

UPDATE products_attributes SET options_name_price = '20.0000' WHERE options_name_id = "YOUR_PRODUCTS_VAULE_ID";

Are you trying to change all of the Colors to $20.00, meaning Red Orange, Yellow, Green etc., or just all of the Color Red to $20.00 ...

Neither. I'm trying to adapt this for our purposes. We need to change the prices of all instances of a particular option. Lets say it has an Option ID of 2. Our options use the default, file, value because they do not have options themselves, but rather represent an add-on product.

So to answer your question, I believe we would be changing all the COLORS or in our case ITEM 1s to $20.00 or whatever price we determine.

The products_attributes table has 3 key fields:
products_id the Product itself
options_id the Option Name ID
options_values_id the Option Value ID

You can update the options_values_price field, the attribute price, based on how you reference this ...

If the Option Name, in your case ITEM, has the options_d of 3 ... then you can use:

UPDATE products_attributes SET options_values_price = 20.00 WHERE options_id = 3;

And all Attributes in the products_attributes table that have the options_id of 3 will have the price set to 20.00, regardless of the options_values_id ...

Be sure to backup your database before trying this ...

Thank you so much! This is incredibly helpful!

It makes me wonder though why there isn't an option to put a value of 'price' to an option. It sucks having individual prices for options under individual products. How hard would it be to request this feature in the future?

It is on the To Do List ... so one of these days ... :smile: