Bulk prices update of Magento2 products using Mysql

Sometimes, you need to update a whole list of products at once and to achieve this in Magento 2, you can come up with the following ideas :

  • using a module,
  • exporting/importing updated products,
  • any manual method
  • hitting through the right Mysql tables

Now depending on your skills, you might choose one option over another but if you really have a lot products prices to update, the Mysql queries is the fastest way to achieve your goal, and it is quite simple actually.

Let’s say (it happened to me) you have all products set up with tax included and you wish to correct it so they are set up with no tax. At 20% VAT, I need to update my prices as follow : price = price / 1.2 (simple math !).

Magento2 Tables which are involved

If you take a look at the tables structure in Magento2, you will notice the presence of the catalog_product_index_price table which gathers all prices for all products. Ok, so let’s just update the fields by issuing the following command :


UPDATE catalog_product_index_price SET price = price / 1.2,min_price=min_price/1.2,max_price=max_price/1.2,final_price=final_price/1.2 WHERE entity_id =100;

The above query only updates one product with entity ID 100. For all products, skip the WHERE entity_id = 100.

At that moment, if you go back to your website, you won’t see any changes, and if you reindex the Magento indexes, all your updates will be canceled.

We need to update another table so updates are taken correctly : catalog_product_entity_decimal

Enter the following query :


UPDATE catalog_product_entity_decimal SET value = value / 1.2 WHERE entity_id = 100;

Ok now we are good, if you are in developer mode you will see the changes right away, if not then clean up your caches.

See how I did the whole update process :

Prices update Magento2

Prices update Catalog Magento2

 

When I reach the website, either in the admin interface of the frontend, all prices are correctly set.

At least you learnt something today, I hope so… That was simple and short, stay tune for additional Magento2 tips, you may browse other magento2 tutorials here on Linkstraffic.

2 responses to “Bulk prices update of Magento2 products using Mysql”

  1. Never update catalog_product_index_price table directly as it will be overwritten by the indexer.
    Please follow this article in order to find the fastest and easiest way to bulk update the prices in Magento2
    https://gist.github.com/MagePsycho/8fae2823235915f7a2bb615e2b332072

  2. Raj KB says:

    Thanks for sharing the pure SQL approach. You also also use CSV for updating the prices as explained belown:
    https://gist.github.com/MagePsycho/8fae2823235915f7a2bb615e2b332072

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Want more information?

Related links will be displayed within articles for you to pick up another good spot to get more details about software development, deployment & monitoring.

Stay tuned by following us on Youtube.

%d bloggers like this: