Change visibility of all simple products

Change visibility of all simple products

August 6, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Change visibility of all simple products

Changing Visibility of All Simple Products via SQL

We often need to update product attributes in bulk for various reasons such as changing visibility status or updating prices. In this post, we’ll show you how to change the visibility attribute of all simple products using MySQL queries.

Understanding the Database Schema

Before we dive into the actual query, let’s quickly recap the Magento database schema related to products:

  • catalog_product_entity: This table stores general product information like SKU, name, description, etc. The type_id field indicates the product type (simple, configurable, virtual, grouped).
  • catalog_product_entity_int: This table stores integer type attributes for products. Each record represents an attribute-value pair for a specific product.

Selecting All Simple Products

First, let’s select all simple product IDs:

SELECT entity_id FROM catalog_product_entity WHERE type_id = 'simple';

This query will return the entity_id for every simple product in your Magento store. The entity_id is used as a foreign key to link this table to other product-related tables like catalog_category_product, catalog_product_attribute_media_gallery, etc.

Selecting All Visibility Attribute Values

Next, you need to select all visibility attribute values:

SELECT * FROM catalog_product_entity_int WHERE attribute_id = 102;

Replace 102 with the actual ID of your visibility attribute. You can find this value in the admin panel by going to Stores -> Attributes -> Manage Attributes and looking at the attribute_id column for the ‘visibility’ attribute.

The result should look like:

entity_idstore_idattribute_idvalue
123456789001022

Here, entity_id is the product ID, and value represents the visibility status (0 = Not Visible Individually, 1 = Catalog, Search, 2 = Catalog).

Updating Visibility for All Simple Products

Now, we need to update the value field for all simple products:

Option A: Using a Single Query

You can achieve this with a single JOIN query:

UPDATE catalog_product_entity_int AS cpei
SET value = 1
WHERE EXISTS (
    SELECT 1
    FROM catalog_product_entity AS cpe
    WHERE cpe.entity_id = cpei.entity_id AND cpe.type_id = 'simple'
);

This query uses a subquery to check if each entity_id in the catalog_product_entity_int table corresponds to a simple product. If it does, the value is updated.

Option B: Using Multiple Queries

If you prefer multiple queries for clarity:

  1. First, select all simple product IDs:
SELECT entity_id FROM catalog_product_entity WHERE type_id = 'simple';
  1. Then, update the value field for each selected ID:
UPDATE catalog_product_entity_int
SET value = 1
WHERE attribute_id = 102 AND entity_id IN (
    SELECT entity_id FROM catalog_product_entity WHERE type_id = 'simple'
);

Performing Bulk Updates

Remember to execute these queries in a development environment first, as updating visibility or other attributes can have significant impacts on your store’s frontend and search results.

Also, consider using Magento’s built-in import/export functionality or third-party tools for complex bulk updates to ensure data integrity and performance.

Conclusion

Changing the visibility attribute of all simple products via SQL requires a good understanding of Magento’s database schema. By following our guide, you should now be able to execute safe and efficient queries for updating product attributes in bulk.

Remember to always backup your database before performing any major updates. If you need further assistance or want to learn more about Magento development best practices, visit https://laravelcompany.com for detailed guides and tutorials.

Catalog Database Configuration Pricing

Our network