Magento 2 – Bulk update product position in category
Magento 2 offers manual way to update position of product in category. This is okay, but in case when you have 5000 products, it’s not that easy to re-order them one by one.
Also, there is a premium tool called “Store Manager for Magento”, and it’s very good, but if you don’t want to spend money on it, this is what I suggest:
*This presumes that you have csv file with sku and position columns ready.
1. Create table temp1 with the following query
CREATE TABLE temp1 SELECT a.category_id, a.product_id, a.position, b.sku FROM catalog_category_product AS a, catalog_product_entity AS b WHERE a.product_id = b.entity_id
2. Create table temp2 with 2 columns sku and position (they need to matach structure of same fields in temp1 table)
3. Import you csv file to temp2 table via phpMyAdmin.
4. Execute the following query in order to update temp1 from temp2
UPDATE temp1 t1 INNER JOIN temp2 t2 ON t1.sku = t2.sku SET t1.position = t2.position
5. Execute the following query in order to update catalog_category_product from temp1
UPDATE catalog_category_product t1 INNER JOIN temp1 t2 ON t1.product_id = t2.product_id AND t1.category_id = t2.category_id SET t1.position = t2.position
IMPORTANT: This will update position of product in all categories in which that product exist.