Scott Donnelly

Magento SQL query to set a base image for any products that do not have one set.

I had to do some work on a Magento site today, where the site admin had added images to all the products, but had not set any of them as base, small, or thumb images - so none of the listings were showing an image on the front end. I googled for a SQL query to do the job, and found none - so I knocked this one up - maybe someone else will find it useful.

The queries do not affect any products that already have the respective values already set.

Base Images:

INSERT INTO catalog_product_entity_varchar (entity_type_id, attribute_id, store_id, entity_id, value) SELECT 4, 79, 0, cpev.entity_id, cpemg.value FROM catalog_product_entity_varchar AS cpev INNER JOIN catalog_product_entity_media_gallery AS cpemg ON cpev.entity_id = cpemg.entity_id WHERE cpev.entity_id NOT IN (SELECT entity_id FROM catalog_product_entity_varchar WHERE entity_type_id = 4 AND attribute_id = 79 AND value IS NOT NULL AND value !=  'no_selection')

Small Images:

INSERT INTO catalog_product_entity_varchar (entity_type_id, attribute_id, store_id, entity_id, value) SELECT 4, 80, 0, cpev.entity_id, cpemg.value FROM catalog_product_entity_varchar AS cpev INNER JOIN catalog_product_entity_media_gallery AS cpemg ON cpev.entity_id = cpemg.entity_id WHERE cpev.entity_id NOT IN (SELECT entity_id FROM catalog_product_entity_varchar WHERE entity_type_id = 4 AND attribute_id = 80 AND value IS NOT NULL AND value !=  'no_selection')

Thumb Images:

INSERT INTO catalog_product_entity_varchar (entity_type_id, attribute_id, store_id, entity_id, value) SELECT 4, 81, 0, cpev.entity_id, cpemg.value FROM catalog_product_entity_varchar AS cpev INNER JOIN catalog_product_entity_media_gallery AS cpemg ON cpev.entity_id = cpemg.entity_id WHERE cpev.entity_id NOT IN (SELECT entity_id FROM catalog_product_entity_varchar WHERE entity_type_id = 4 AND attribute_id = 81 AND value IS NOT NULL AND value !=  ‘no_selection’)

Hopefully this can save someone else a bit of time!