Change the visibility of all simple products

Question

I am trying to change the visibility of all SIMPLE products only through SQL.

This is how I select all visibility attribute values:

SELECT * FROM `catalog_product_entity_int` WHERE attribute_id = 102 

Table

catalog_product_entity_int has an entity_id column, which is the identifier of the product object, and value , which should be changed to 1 for all simple products.

This is how I choose all simple products:

 SELECT * FROM `catalog_product_entity` WHERE `type_id`= 'simple' 

What I cannot do is write a query that updates the value catalog_product_entity_int column for all simple products.

Answer 1

as i got ur question, this simple code will work for u.

I assume the relationship between these two tables is: -entity_id

 update catalog_product_entity_int set value =1 where attribute_id = 102 and entity_id= (select entity_id from `catalog_product_entity` where `type_id`= 'simple') 

Answer 2

Do you have a reason for editing an EAV attribute with a direct SQL query?

You can do the same with collections or even in the admin backend: directory - manage products - enter "Simple product" - select all - Actions - Update attributes - Visibility

Answer 3

Exactly working request

 UPDATE catalog_product_entity_int CPI INNER JOIN catalog_product_entity CP ON CPI.entity_id = CP.entity_id SET value = 1 WHERE CPI.attribute_id = 102 AND CP.type_id = 'simple'; 

Answer 4

You want to use one of the massive actions of Magento. Thus, the correct events are still dispatched.

 <?php // Get all simple product ids $ids = Mage::getResourceModel('catalog/product_collection') ->addAttributeToFilter('type_id', array('eq' => Mage_Catalog_Model_Product_Type::TYPE_SIMPLE)) ->getAllIds(); // Update their visibility Mage::getSingleton('catalog/product_action')->updateAttributes( $ids, array("visibility" => Mage_Catalog_Model_Product_Visibility::VISIBILITY_NOT_VISIBLE), Mage_Core_Model_App::ADMIN_STORE_ID ); 

Answer 5

Follow the instructions.

Step 1: Before you make any changes to the query, you need to make a backup copy of the produced table or database. In this case, take the backup table (catalog_product_entity_int).

Step 2: Find the exact identifier of the visibility attribute in the eav_attribute table.

( SELECT * FROM eav_attribute WHERE attribute_code LIKE 'visibility' ). Then save this value in the following query ...

Step 3. Run this query

UPDATE catalog_product_entity_int SET VALUE =2 WHERE attribute_id = 102 AND entity_id IN (SELECT entity_id FROM catalog_product_entity WHERE type_id= 'simple')

Good luck ...

Answer 6

I think this is a sql command, which is for sure:

 UPDATE catalog_product_entity_int SET VALUE =2 WHERE attribute_id = 93 AND entity_id IN (SELECT entity_id FROM catalog_product_entity WHERE type_id= 'simple') 

Note. Check your attribute attribute number.