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.

Leave a Reply

Want more information?

Related links will be displayed here in this section for you to pick up another good spot to get more details about Web marketing and Search Engine Optimization. There will be some sites which we selected to ease the work of any webmaster or/and web marketer on the Internet.

%d bloggers like this: