(SQL) Batch update prices for products with X criteria...

[EvanGR]

I need to batch update the prices on a collection of products.
The criteria is that the SKUs begin with a specific prefix e.g. "TDS-".

Is this something that can be easily done through an SQL statement?
I am asking because prices are in a different table and my SQL knowledge doesn't reach that far.

Any help appreciated, thanks.

[Studio 42]

You can use my tool https://shop.st42.fr/en/products/vm-be-pro-batch-product-edit.htm and use a multiplicator for eg. x1.2 to add 20%

[AH]

Yes pretty simple really
BUT WATCH OUT BECAUSE GETTING THIS WRONG IS A REAL FAST WAY TO SCREW YOUR SITES PRICES!!!!!!!!!!!!!

I have and SQL update that also restricts by manufacturer name - (you could easily add shoppergroup ID restrictions as well) - I have my own pricing import using SKU (not commercially available) and tend to use that more often.

This SQL just makes things quicker if a complete range is being uplifted in price (usually due to a manufacturers across the board cost price increase)

You can remove / adjust lots of bits of this - but it is a decent starting point

Code Select Expand


update yourname_virtuemart_product_prices as c


LEFT JOIN yourname_virtuemart_products AS p ON p.virtuemart_product_id = c.virtuemart_product_id
LEFT JOIN yourname_virtuemart_product_manufacturers AS m ON m.virtuemart_product_id = p.virtuemart_product_id
LEFT JOIN yourname_virtuemart_manufacturers_en_gb AS ml ON ml.virtuemart_manufacturer_id = m.virtuemart_manufacturer_id

# NOW SET SOMETHING set c.product_price = round(c.product_price,2)
#incerase price by 5% (rounded 2dp)
SET c.product_price = round(c.product_price * 1.05,2)

#WHERE THE sku and manufacturer are like
WHERE (ml.mf_name = "" and p.product_sku LIKE "TDS-%")  OR (ml.mf_name = "" and p.product_sku LIKE "TDS-%")



To meet you specific need for SKUs starting with xxx this adds 5% to existing prices

Code Select Expand


UPDATE yourname_virtuemart_product_prices AS c

LEFT JOIN yourname_virtuemart_products AS p ON p.virtuemart_product_id = c.virtuemart_product_id

# NOW SET SOMETHING e.g. increase price by 5% (rounded 2dp)
SET c.product_price = round(c.product_price * 1.05,2)

#WHERE THE sku is like
WHERE p.product_sku LIKE "TDS-%"