Magento: Bulk change product store id

Magento: Bulk change product store id

July 19, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento: Bulk change product store id

Bulk Change Product Store ID in Magento: A Comprehensive Solution

Hello developers,

One of the most common issues faced by Magento users is accidentally setting the wrong store ID during product import. In this post, I’ll guide you through a step-by-step process to bulk update your product’s store ID using SQL queries.

Understanding the Problem

Let’s first understand what’s happening. When you import products, each row corresponds to a single product record in the catalog_product_entity table. The store_id column determines which website (or store view) the product belongs to. If this value is set incorrectly or not specified during import, it defaults to 0.

Solution Approach

The solution involves running a series of SQL queries to update the store_id across all related tables for each product entity. I’ll provide you with the corrected version of the query found in the Magento board and explain how to execute it properly.

Corrected SQL Query

Here’s the complete, corrected SQL query that should work without syntax or unknown column errors:

-- Update catalog_product_store table
REPLACE INTO catalog_product_store (store_id, product_id) SELECT 1, entity_id FROM catalog_product_entity WHERE store_id = 0;

-- Update all other related tables
UPDATE catalog_category_entity SET store_id = 1 WHERE store_id = 0;
UPDATE catalog_product_entity SET store_id = 1 WHERE store_id = 0;
UPDATE catalog_product_entity_datetime SET store_id = 1 WHERE store_id = 0;
UPDATE catalog_product_entity_decimal SET store_id = 1 WHERE store_id = 0;
UPDATE catalog_product_entity_gallery SET store_id = 1 WHERE store_id = 0;
UPDATE catalog_product_entity_int SET store_id = 1 WHERE store_id = 0;
UPDATE catalog_product_entity_text SET store_id = 1 WHERE store_id = 0;
UPDATE catalog_product_entity_tier_price SET store_id = 1 WHERE store_id = 0;
UPDATE catalog_product_entity_varchar SET store_id = 1 WHERE store_id = 0;

Step-by-Step Execution

  1. Backup Your Database: Before proceeding, always take a complete backup of your Magento database to prevent data loss.

  2. Open MySQL Query Editor: Use a tool like phpMyAdmin, Navicat, or the command line to connect to your Magento database as an administrator user with write permissions.

  3. Execute the Queries: Copy and paste the entire query above into the SQL editor one at a time. Make sure to replace 1 with the desired store ID before running each query.

  4. Verify the Results: After executing all queries, log in to your Magento backend and verify if the product store IDs have been updated correctly.

Best Practices

  • Always test changes on a staging environment before applying them to production.
  • Monitor your website’s performance during and after the update process.
  • Consider using third-party tools like the Magento Data Migration Tool or extensions for bulk data updates in future.

Remember, while SQL queries are powerful, they also pose risks if executed incorrectly. Always double-check your syntax, table names, and column names before running any update query on a live database.

Conclusion

Bulk updating product store IDs is a common task that requires careful execution. By following the corrected SQL queries provided in this post and adhering to best practices, you can easily fix wrongly assigned store IDs without losing product data or causing downtime.

If you have any other questions about Magento development, feel free to explore our Magento Development Services for expert guidance. Happy coding!

Catalog Database Configuration Pricing

Our network