[Skip to main content](#vessel-content) # Change product variants prices. ## - **[starko](/forum/user/59129-starko "View starko's Profile")** - [![starko's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/59129-starko "View starko's Profile") Topic Author - [ Offline](/forum/user/59129-starko) Less More - Posts: 12 - Thank you received: 0 - - 6 years 5 months ago - 6 years 5 months ago [\#319535](#319535) by [starko](/forum/user/59129-starko "View starko's Profile") *[Change product variants prices.](/forum/44-mass-actions/899945-change-product-variants-prices#319535 "Hello. Could you help with the make of the SQL query? It seems to me that this cannot be done with the help of mass actions. My product has some characteristics. I need to change the price of a product variant with a certain characteristic, adding a constant (5) to the main price. A change in price is required for all products that have this characteristic! Thanks.")* was created by *[starko](/forum/user/59129-starko "View starko's Profile")* Hello. Could you help with the make of the SQL query? It seems to me that this cannot be done with the help of mass actions. My product has some characteristics. I need to change the price of a product variant with a certain characteristic, adding a constant (5) to the main price. A change in price is required for all products that have this characteristic! Thanks. Last edit: 6 years 5 months ago by [starko](/forum/user/59129-starko "View starko's Profile"). Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzQ0LW1hc3MtYWN0aW9ucy84OTk5NDUtY2hhbmdlLXByb2R1Y3QtdmFyaWFudHMtcHJpY2VzP3RtcGw9bWFya2Rvd24=) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Offline](/forum/user/63-nicolas) Less More - Posts: 86556 - Thank you received: 14304 - - 6 years 5 months ago [\#319550](#319550) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[Change product variants prices.](/forum/44-mass-actions/899945-change-product-variants-prices#319550 "Hello. Could you help with the make of the SQL query? It seems to me that this cannot be done with the help of mass actions. My product has some characteristics. I need to change the price of a product variant with a certain characteristic, adding a constant (5) to the main price. A change in price is required for all products that have this characteristic! Thanks.")* Hi, This indeed can't be done with a mass action as the filter on characteristic will only target the main products, not the variants, and thus you can't change the price of the variants if you want to restrict it to only a particular characteristic. Regarding a MySQL query, something like that: Code: UPDATE #__hikashop_variant AS v LEFT JOIN #__hikashop_product AS p ON v.variant_product_id = p.product_id LEFT JOIN #__hikashop_characteristic AS c ON v.variant_characteristic_id = c.characteristic_id LEFT JOIN #__hikashop_price AS price ON p.product_id = price.price_product_id SET price.price_value = price.price_value + 5 WHERE c.characteristic_parent_id = XXX AND p.product_type = 'variant' where XXX is to be replaced with the id of the characteristic. Of course, I recommend to make a backup before so that you can easily roll back if it doesn't work like you want. The following user(s) said Thank You: [starko](/forum/user/59129-starko "View starko's Profile") Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzQ0LW1hc3MtYWN0aW9ucy84OTk5NDUtY2hhbmdlLXByb2R1Y3QtdmFyaWFudHMtcHJpY2VzP3RtcGw9bWFya2Rvd24=) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[starko](/forum/user/59129-starko "View starko's Profile")** - [![starko's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/59129-starko "View starko's Profile") Topic Author - [ Offline](/forum/user/59129-starko) Less More - Posts: 12 - Thank you received: 0 - - 6 years 5 months ago [\#319642](#319642) by [starko](/forum/user/59129-starko "View starko's Profile") Replied by *[starko](/forum/user/59129-starko "View starko's Profile")* on topic *[Change product variants prices.](/forum/44-mass-actions/899945-change-product-variants-prices#319642 "Hello. Could you help with the make of the SQL query? It seems to me that this cannot be done with the help of mass actions. My product has some characteristics. I need to change the price of a product variant with a certain characteristic, adding a constant (5) to the main price. A change in price is required for all products that have this characteristic! Thanks.")* Yes, all this works fine (c.characteristic_id) Thanks, Nicolas. But there's a problem) There are no records in the price table, that is, I have to make an insert for each record I need with this characteristic. Do I have to write a PHP loop for multiple insertion? Or is there another possibility? Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzQ0LW1hc3MtYWN0aW9ucy84OTk5NDUtY2hhbmdlLXByb2R1Y3QtdmFyaWFudHMtcHJpY2VzP3RtcGw9bWFya2Rvd24=) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Offline](/forum/user/63-nicolas) Less More - Posts: 86556 - Thank you received: 14304 - - 6 years 5 months ago [\#319675](#319675) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[Change product variants prices.](/forum/44-mass-actions/899945-change-product-variants-prices#319675 "Hello. Could you help with the make of the SQL query? It seems to me that this cannot be done with the help of mass actions. My product has some characteristics. I need to change the price of a product variant with a certain characteristic, adding a constant (5) to the main price. A change in price is required for all products that have this characteristic! Thanks.")* Hi, That's indeed correct. You need a loop in PHP to insert all the entries in the hikashop_price table. My MySQL query will only work supposing that all the variants already have a price. Alternatively, for the variants without a price, if you want to add the same value for all of them, then you can add it directly in the main product and the price will be user dynamically by the variants without price. But as I don't know your situation, this might not be a solution to your problematic. Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzQ0LW1hc3MtYWN0aW9ucy84OTk5NDUtY2hhbmdlLXByb2R1Y3QtdmFyaWFudHMtcHJpY2VzP3RtcGw9bWFya2Rvd24=) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[starko](/forum/user/59129-starko "View starko's Profile")** - [![starko's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/59129-starko "View starko's Profile") Topic Author - [ Offline](/forum/user/59129-starko) Less More - Posts: 12 - Thank you received: 0 - - 6 years 5 months ago [\#320030](#320030) by [starko](/forum/user/59129-starko "View starko's Profile") Replied by *[starko](/forum/user/59129-starko "View starko's Profile")* on topic *[Change product variants prices.](/forum/44-mass-actions/899945-change-product-variants-prices#320030 "Hello. Could you help with the make of the SQL query? It seems to me that this cannot be done with the help of mass actions. My product has some characteristics. I need to change the price of a product variant with a certain characteristic, adding a constant (5) to the main price. A change in price is required for all products that have this characteristic! Thanks.")* Code: 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. Code: 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 Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzQ0LW1hc3MtYWN0aW9ucy84OTk5NDUtY2hhbmdlLXByb2R1Y3QtdmFyaWFudHMtcHJpY2VzP3RtcGw9bWFya2Rvd24=) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Offline](/forum/user/63-nicolas) Less More - Posts: 86556 - Thank you received: 14304 - - 6 years 5 months ago [\#320058](#320058) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[Change product variants prices.](/forum/44-mass-actions/899945-change-product-variants-prices#320058 "Hello. Could you help with the make of the SQL query? It seems to me that this cannot be done with the help of mass actions. My product has some characteristics. I need to change the price of a product variant with a certain characteristic, adding a constant (5) to the main price. A change in price is required for all products that have this characteristic! Thanks.")* Hi, We were reported a similar issue recently with the mass action system on characteristics: [ www.hikashop.com/support/forum/mass-acti...ql-error.html#319877](https://www.hikashop.com/support/forum/mass-actions/900010-product-characteristics-mass-action-sql-error.html#319877) We've added a patch on our end for that. Download again the install package of HikaShop on our website and install it on your website and it should hopefully work properly. Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzQ0LW1hc3MtYWN0aW9ucy84OTk5NDUtY2hhbmdlLXByb2R1Y3QtdmFyaWFudHMtcHJpY2VzP3RtcGw9bWFya2Rvd24=) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. Board Categories HikaShop Install & Update Product & Category Display Content Creation Sales and Orders Orders management Reports & Statistics Customers Checkout Payment Methods Shipping Methods Import & Export (CSV, XLS…) Specific Features Affilation Taxes Wishlist Vote Carousel & Slider Mass Actions Filters & Search Discount & Coupon Feature Request Integrations Showcase General Talk about HikaShop Development HikaShop 3.0 Beta Translation Commercial Jobs HikaShop Components HikaMarket Market: Feature requests Market: How to? Market: Bug Report HikaSerial General talk about HikaSerial Serial: Bug Report Serial: How to? Support en Français de HikaSerial (French Support) HikaSubscription HikaAuction Auction: Feature requests Auction: How to? Auction: Bug report Hikashop around the world Support en Français (French Support) Russian FAQ. Курс выживания с Hikashop. Частые вопросы и о Расширения Hikashop Наши сайты на Hikashop 日本語版サポート(Japanese Support) Thai German Archives Bug Report How to? [](/forum/topics/feed/mode-topics?format=feed&type=rss) Time to create page: 0.059 seconds [Powered by](/forum/credits) [Kunena Forum](https://www.kunena.org)