Author Topic: (SQL) Batch update prices for products with X criteria...  (Read 1078 times)

EvanGR

  • Jr. Member
  • **
  • Posts: 476
(SQL) Batch update prices for products with X criteria...
« on: March 11, 2020, 10:17:37 am »
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

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 4619
  • Joomla & Virtuemart developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3
Re: (SQL) Batch update prices for products with X criteria...
« Reply #1 on: March 11, 2020, 10:27:20 am »
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

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3510
  • VirtueMart Version: 3.8.9
Re: (SQL) Batch update prices for products with X criteria...
« Reply #2 on: April 06, 2020, 17:57:54 pm »
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]
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]
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-%"
Regards
A

Joomla 3.9.27
php 7.4