magento2.1 bulk price update

Question

I need to increase all the prices in my store by a fixed percentage. What would be the best way to achieve this?

Answer 1

I had the same question, and I used SQL (phpMyAdmin) to solve this in Magento 2.1.4. This can be done in three steps, no coding and no extension needed. Please be aware, that your tables might have a prefix, and values may differ. But this worked well for me.

I recommend a database backup before doing this.

1) Get a list of all your SKUs, product prices and corresponding value_id's

I have added product name and product type as well. Most important though is the value_id from the table "mgct_catalog_product_entity_decimal", which we need to update prices later.

SELECT   p.value_id
       , e.sku AS SKU
       , t.value AS PROD_NAME
       , e.type_id AS PROD_TYPE
       , p.value AS PRICE
FROM catalog_product_entity_decimal p
LEFT JOIN catalog_product_entity e ON p.entity_id = e.entity_id
LEFT JOIN catalog_product_entity_text t ON p.entity_id = t.entity_id
WHERE p.attribute_id = 77   -- 77 = Price
  AND t.store_id = 0        --  0 = General Store View / You can specify a store view here
  AND t.attribute_id = 85   -- 85 = Product Name

The values for attribute_id's may vary in your system!

2) Update prices

Here you set the price for each value_id. It's simply done with a CASE-WHEN Statement.

UPDATE catalog_product_entity_decimal
SET value = CASE
              WHEN value_id = 3  THEN 83.40
              WHEN value_id = 10 THEN  6.90
            END
WHERE value_id IN (3, 10) -- List ALL concerned value_id's here! VERY IMPORTANT!!

If you need to update a lot of prices, then you can generate the CASE-WHEN statement by using excel.

3) Reindex: magento indexer:reindex

Thank you for your feedback if this works for you guys as well. IMHO it's a good alternative to installing extensions or changing the code.

Answer 2

You need to write direct queries to update price. Because if you will loop collection and there are more than 1000 products then it will take time to update price or script can fail.

Or here is one module for updating product price in percentage or fixed amount.

For Magento 1.x

http://www.gagglethread.com/extensions/mass-price-updater.html

For Magento 2.x

https://marketplace.magento.com/gaggle-priceupdate.html http://www.gagglethread.com/magento2/extensions/magento2-bulk-price-updater.html

Answer 3

This method worked for me on 2.1 but be aware it triggers a Magento bug that requires a MySQL script to work for now...read to the end before starting so you know what you're in for -

Method:

Backup Database

Export Products

1. Dashboard: System(left menu) > Export
2. Select -
   • "Entity Type": Product
   • "Export File Format": CSV
3. Tick everything except "Price" & "SKU" in the "Exclude" column
4. Click "Continue"

Prepare CSV file (in MS Excel)

1. Delete extra columns except "Price" & "SKU"
2. Insert a new (temporary) column next to price
3. add a formula to update your price
4. select the formula and "Fill down" for all rows
5. copy the results of the formula
6. Paste into the original column using "Paste Special"(edit) menu > select "values only"
7. delete the temporary column
8. Save as CSV - if using a Mac select Save as "Windows Comma Separated(.csv)"

Note: the last step prevents an error when the data is validated in Magento. See more here - https://magento.stackexchange.com/a/48233/49317

Importing Products

1. Dashboard: System(left menu) > Import
2. Select
   • Entity Type: Products
   • Import Behaviour: Add/Update
   • leave other options as default
3. Select file
4. Click "Check Data"
5. If there are no issues click "Import"

Cleaning database

When you go to the catalog admin in you'll see an error like this "Item (Magento\Catalog\Model\Product\Interceptor) with the same ID "xxx" already exists."

This is a known issue in Magento 2.x and the fix by Yonn-Trimoreau can be found here:

https://github.com/magento/magento2/issues/5418

DELETE FROM cataloginventory_stock_item WHERE product_id IN ( SELECT * FROM( SELECT product_id FROM cataloginventory_stock_item GROUP BY product_id HAVING COUNT( product_id ) >1 )tblTMP WHERE website_id = 1 )

Answer 4

If you are able to write code or find someone to code for you, then follow below procedure:

• Create a new action in any controller of your custom module.
• Create an object of catalog/product model and get collection of all products.
• Loop through all products in collection, load each product by its id, set price = current price + (current price * percentage) and save product.

Please let me know if you find any problem.

Thanks

Answer 5

I usually do it like that: exporting only price and sku, open csv in calc, transform prices, save and import again.

Answer 6

AV_MassPriceUpdater:

You can update the different price type as regular price or special price and you can change the prices fixed or with percentage per category

https://github.com/adamvarga/AV_MassPriceUpdater

Answer 7

You can easily increase the price for all the stores via SQL as

SET @INCREMENT_VALUE = 10;
UPDATE catalog_product_entity_decimal SET value = value + @INCREMENT_VALUE WHERE attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'price' AND entity_type_id = 4);

CAUTION: Make sure to take backup.

If you want an easier approach via the admin interface, then you can use the Magento 2 Regular, Special & Tier Price Importer Extension.
Just export the products with the required price types and in the price-column use the value as +10 or any required increment or decrement value.

Answer 8

The fastest way to import price in Magento 2. Other scripts are also available.

Just Prepare sheet and boom with script.!!!

https://github.com/Extrembler/Magento-2-Import-Scripts

Answer 9

I am doing things like this always in a spreadsheet.

1. Export to the spreadsheet.
2. Import back to Magento.

There are several extensions for this: https://spreadsheet-master.com/, https://firebearstudio.com/, https://amasty.com/import-and-export-for-magento-2.html