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.
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%
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
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
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-%"