UPDATE `jos_hikashop_price` as var
right join jos_hikashop_product as pvar on pvar.product_id=var.price_product_id
right join jos_hikashop_variant as vvar on vvar.variant_product_id=pvar.product_id
left JOIN (
SELECT *
FROM `jos_hikashop_price` as main
left join jos_hikashop_product as pmain on pmain.product_id=main.price_product_id
where pmain.product_type='main'
) as main on main.product_id=pvar.product_parent_id
set var.price_value = main.price_value + 5
where pvar.product_type='variant' and vvar.variant_characteristic_id=xxx
Today I worked with this and saw that the query does not do what I need.
Your SQL query adds a constant to the value of the variant.
I need a more flexible solution, depending on the base price of the product.
This new code works, although it still requires the existence of an record of variant price.
Non-existent prices can be added through mass actions!
I did it by editing the price with filtering records. If the record does not exist, your code adds a new record to the price table.
Unfortunately filtering the characteristics does not work, this would simplify the task.
An error has occurred.
1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`` = '1' AND hk_characteristic_parent3.characteristic_value = 'Wood handle')' at line 1