Magento disable products with no images via Phpmyadmin

Question

i would like to disable products with no images in Magento 1.8. I have tried this code:

UPDATE catalog_product_entity_int SET value = 2 
WHERE attribute_id = 4  
  AND entity_id IN (      
        SELECT entity_id 
        FROM catalog_product_entity_media_gallery
        RIGHT OUTER JOIN catalog_product_entity ON catalog_product_entity.entity_id = catalog_product_entity_media_gallery.entity_id 
        WHERE catalog_product_entity_media_gallery.value is NULL
  );

but i have this alert:

Column 'entity_id' in field list is ambiguous

How can i resolve?

Thanks!

Stefan Brendle Stefan Brendle · Accepted Answer · 2013-10-31T13:49:10

In your inner query on line 4 you're listing the column entity_id. This column name entity_id is not unique in your sql field list, because the column entity_id is in the catalog_product_entity table and in the catalog_product_entity_media_gallery as well. MySQL simply doesn't know, which one of these two columns should be shown. So you have to prepend the table in your select area:

UPDATE catalog_product_entity_int SET value = 2 
WHERE attribute_id = 4  
  AND entity_id IN (      
        SELECT `your_table_name`.`entity_id` 
        FROM catalog_product_entity_media_gallery
        RIGHT OUTER JOIN catalog_product_entity ON catalog_product_entity.entity_id = catalog_product_entity_media_gallery.entity_id 
        WHERE catalog_product_entity_media_gallery.value is NULL
  );

Magento disable products with no images via Phpmyadmin

1 Answers