Change visibility of all simple products

Question

I'm trying to change visibility of all SIMPLE-only products via SQL.

Here is how I select all values of visibility attribute:

SELECT * FROM `catalog_product_entity_int` WHERE attribute_id = 102

catalog_product_entity_int table has entity_id column which is product entity ID, and value that should be changed to 1 for all simple products.

Here is how I select all simple products:

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

What I can't do, is write query that would update value column of catalog_product_entity_int for all simple products.

Answer 1

Do you have any reason to edit EAV attribute with direct sql query?

You could do the same using collections or even at admin backend: catalog - manage products - type "Simple product" - Select all - Actions - Update Attributes - Visibility

Answer 2

Exactly working query

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';